Grouping Consecutive Numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Grouping Consecutive Numbers

Author

Topic

klanda20
Starting Member

5 Posts

Posted - 2011-06-29 : 07:28:51

Hello Everyone,

Here is issues. 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
42 100001 0 1
43 100001 1 1
44 100001 2 1
45 100001 3 0
46 100001 4 1
57 100001 5 1
58 100001 6 0
59 100001 7 1
60 100001 8 1
61 100001 9 0
62 100001 10 1
63 100001 11 1
64 100001 12 0

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

Thanks in advance!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-29 : 07:49:46

This is a variation of the islands and gaps problem. You can find many solutions to this if you google. One way is as follows - I may not have the exact logic you are looking for (for example, not clear to me if you want to find all the start days for consecutive groups of 3 or more):


-- TEST DATA
CREATE TABLE #tmp (id int, stnum int, realDay INT, pass INT);

insert into #tmp values (42,100001,0,1);
insert into #tmp values (43,100001,1,1);
insert into #tmp values (44,100001,2,1);
insert into #tmp values (45,100001,3,0);
insert into #tmp values (46,100001,4,1);
insert into #tmp values (57,100001,5,1);
insert into #tmp values (58,100001,6,0);
insert into #tmp values (59,100001,7,1);
insert into #tmp values (60,100001,8,1);
insert into #tmp values (61,100001,9,0);
insert into #tmp values (62,100001,10,1);
insert into #tmp values (63,100001,11,1);
insert into #tmp values (64,100001,12,0);

-- QUERY
WITH cte AS
(
   SELECT
      *,
      realday - ROW_NUMBER() OVER(partition by stnum ORDER BY realday) AS grp -- or order by id??
   FROM
      #tmp
   WHERE
      pass = 1
)
SELECT
   MIN(realDay)
FROM
   cte
GROUP BY
   grp
HAVING
   COUNT(grp) >= 3;

-- CLEANUP
DROP TABLE #tmp;

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-29 : 07:49:58

select t1.stnum, MIN(t1.realday)
from tbl t1
join tbl t2
on t1.stnum = t2.stnum and t1.realday = t2.realday-1 and t2.pass = 1
join tbl t3
on t1.stnum = t3.stnum and t1.realday = t3.realday-2 and t3.pass = 1
where t1.pass = 1
group by t1.stnum

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

klanda20
Starting Member

5 Posts

Posted - 2011-06-29 : 08:35:49

Thanks sunitabeck. The only thing I needed to add was the stnum to the outer group by and that's only because I have more than 1 stnum in my full dataset.

For the sample I gave your code worked perfectly.

Thanks again!

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-29 : 11:22:29

Hre is an old cut& paste that might help

INSERT INTO Foobar
VALUES
('2008-01-01', 'X1'), ('2008-02-01', 'X1'),
('2008-03-01', 'X1'), ('2008-04-01', 'X1'),
('2008-05-01', 'X1'), ('2008-06-01', 'X2'),
('2008-07-01', 'X2'), ('2008-08-01', 'X3'),
('2008-09-01', 'X3'), ('2008-10-01', 'X3'),
('2008-11-01', 'X1');

SELECT MIN(vague_object), MIN(event_date), MAX(event_date)
FROM (SELECT vague_object, event_date,
ROW_NUMBER() OVER (ORDER BY vague_object, event_date)
- ROW_NUMBER() OVER (PARTITION BY vague_object
ORDER BY event_date),
ROW_NUMBER() OVER (ORDER BY event_date)
- ROW_NUMBER() OVER (PARTITION BY vague_object
ORDER BY event_date)
FROM Foobar) AS X(vague_object, event_date, grp1, grp2)
GROUP BY grp1, grp2;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources