SQL grouping by id separated by other ids

Question

I use MS SQL Server 2008 R2. I have a table:

    TypeId    EventDate                 Value      
1.  1         2013-11-01 10:13:00.000   50  
2.  1         2013-11-01 10:15:00.000   10  
3.  1         2013-11-01 10:18:00.000   40  
4.  2         2013-11-01 10:19:00.000   12  
5.  2         2013-11-01 10:21:00.000   545 
6.  1         2013-11-01 10:23:00.000   35  
7.  1         2013-11-01 10:27:00.000   47 
8.  3         2013-11-01 10:30:00.000   3
9.  4         2013-11-01 10:31:00.000   0
10. 4         2013-11-01 10:33:00.000   7
11. 4         2013-11-01 10:38:00.000   35
12. 1         2013-11-01 10:41:00.000   91 
13. 5         2013-11-01 10:45:00.000   37
14. 5         2013-11-01 10:48:00.000   35

I'd like to have continuous groups based on TypeId field values, that are not separated by other TypeId values (when ordering is by EventDate).
In this example I want to have seven groups:

• TypeId=1, records 1-3
• TypeId=2, records 4-5
• TypeId=1, records 6-7
• TypeId=3, record 8
• TypeId=4, records 9-11
• TypeId=1, record 12
• TypeId=5, records 13-14

If I use GROUP BY TypeId, I get one group for TypeId=1 consisting of records 1-3, 6-7 and 12

I'd like for example get min and max EventDate for any of these groups:

TypeId  MinEventDate              MaxEventDate
1       2013-11-01 10:13:00.000   2013-11-01 10:18:00.000
2       2013-11-01 10:19:00.000   2013-11-01 10:21:00.000
1       2013-11-01 10:23:00.000   2013-11-01 10:27:00.000
3       2013-11-01 10:30:00.000   2013-11-01 10:30:00.000
4       2013-11-01 10:31:00.000   2013-11-01 10:38:00.000
1       2013-11-01 10:41:00.000   2013-11-01 10:41:00.000
5       2013-11-01 10:45:00.000   2013-11-01 10:48:00.000

Answer 1

Here's an alternative that is fast for big data sets. The CTE calculates the difference between a row number representing (EventDate) ordering and a row number representing (TypeId, EventDate) ordering, and calls that difference grp. Within each consecutive TypeId segment, the difference will be constant, and smaller than the difference that will be produced for the next consecutive TypeId segment.

Then a group of (TypeId, grp) contains all consecutive rows with the same status:

; with  CTE as 
        (
        select  TypeId
        ,       EventDate
        ,       row_number() over (order by EventDate)
                    - row_number() over (order by TypeId, EventDate) as grp
        from     dbo.TestTable
        )
select  min(EventDate) as mn
,       max(EventDate) as mx
,       TypeId
from    CTE
group by 
        TypeId
,       grp
order by 
        mn;

See it working at SQLFiddle

For more details on this method, see this sample chapter of MVP Deep Dives.