Grouping and Getting Count of Consecutive Numbers

• klanda20

  Valued Member

  Points: 73

  More actions

  June 29, 2011 at 5:49 am

  #243386

  Hello Everyone,

  Here is my problem. I have a table with a unique id, person id, day and a column called pass which is 1 or 0. I need to find if the person "passed" on 3 or more consecutive days. If so, I want to return the first row in which this happened.

  Here is a sample table:

  id stnum realDay pass

  4210000101

  4310000111

  4410000121

  4510000130

  4610000141

  5710000151

  5810000160

  5910000171

  6010000181

  6110000190

  62100001101

  63100001111

  64100001120

  I've been trying combinations of the ROW_NUMBER function but can't quite get it working.

  Thanks in advance!

• Mark Cowne

  One Orange Chip

  Points: 26984

  More actions

  June 29, 2011 at 6:49 am

  #1345876

  DECLARE @T TABLE(id INT,stnum INT,realDay INT,pass INT)

  INSERT INTO @T(id,stnum,realDay,pass)

  SELECT 42, 100001, 0, 1 UNION ALL

  SELECT 43, 100001, 1, 1 UNION ALL

  SELECT 44, 100001, 2, 1 UNION ALL

  SELECT 45, 100001, 3, 0 UNION ALL

  SELECT 46, 100001, 4, 1 UNION ALL

  SELECT 57, 100001, 5, 1 UNION ALL

  SELECT 58, 100001, 6, 0 UNION ALL

  SELECT 59, 100001, 7, 1 UNION ALL

  SELECT 60, 100001, 8, 1 UNION ALL

  SELECT 61, 100001, 9, 0 UNION ALL

  SELECT 62, 100001, 10, 1 UNION ALL

  SELECT 63, 100001, 11, 1 UNION ALL

  SELECT 64, 100001, 12, 0;

  WITH CTE1 AS (

  SELECT id,stnum,realDay,pass,

  ROW_NUMBER() OVER(PARTITION BY stnum ORDER BY realDay) AS rn1,

  ROW_NUMBER() OVER(PARTITION BY stnum,pass ORDER BY realDay) AS rn2

  FROM @T),

  CTE2 AS (

  SELECT id,stnum,realDay,pass,

  ROW_NUMBER() OVER(PARTITION BY stnum,rn1-rn2 ORDER BY rn1) AS rn3,

  MIN(realDay) OVER(PARTITION BY stnum,rn1-rn2) AS minrealDay,

  MAX(realDay) OVER(PARTITION BY stnum,rn1-rn2) AS maxrealDay

  FROM CTE1

  WHERE pass=1

  )

  SELECT id,stnum,realDay,pass

  FROM CTE2

  WHERE maxrealDay-minrealDay>=2

  AND rn3=1;

  Also have a read of this

  http://www.sqlservercentral.com/articles/T-SQL/71550

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537