jeudi 29 juin 2017

Count If A and B matches, then numbers in sequence before A changes

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