I have a puzzling question for SQL.
I'm looking to take 2 single criteria (criteria1 and criteria2), test it against a single row criteria in a database, then if they both match, to get a COUNT on rows in a sequence before criteria1 changes.
We have a shared table of consecutive 'serial' type numbers but with different 'part' type numbers and are looking to get a count of rows of consecutive numbers if the first test matches.
Like:
AA 12
AA 13
AA 14
AA 15
BB 16
BB 17
BB 18
AA 19
AA 20
I've tried:
SELECT COUNT(CASE column1 WHEN 'criteria1' THEN 1 ELSE NULL END)
FROM [dbo].[tableName]
WHERE [column1] = 'criteria1' AND [column2] BETWEEN criteria2 AND criteria3
And:
IF EXISTS (SELECT 1 FROM [dbo].[tableName] WHERE
[column1] = 'criteria1' AND [column2] = criteria2)
BEGIN
SELECT COUNT(CASE column1 WHEN 'criteria1' THEN 1 ELSE NULL END)
FROM [dbo].[tableName]
WHERE [column1] = 'criteria1' AND [column2] BETWEEN criteria2 AND criteria3
END
These don't do an initial criteria1 matches and criteria2 matches test before getting the series count, but get the count for matches overall 'between' criteria2 and criteria3, tho not without (consecutive) fault. These also don't look at consecutive matches before criteria1 would change. This can be done via stored procedure or direct statement.
Aucun commentaire:
Enregistrer un commentaire