CODEWITHSUNDEEP
sql-serversql-server-2008-r2grouping
user3060918
user3060918

Reputation: 31

SQL grouping by id separated by other ids

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:

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

Upvotes: 3

Views: 106

Answers (2)

Andomar
Andomar

Reputation: 238086

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.

Upvotes: 1

valex
valex

Reputation: 24144

Try this query:

With T1 as 
(
select T.*,
 (select TOP 1 EventDate
  from Table1 where TypeID<>T.TypeId
                    and 
                    EventDate<T.EventDate
  ORDER BY EventDate DESC)
  as GrpStart
from Table1 as T
)
Select min(TypeId) as TypeId,
       min(EventDate) as MinEventDate,
       max(EventDate) as MaxEventDate
from T1 Group by GrpStart

SQLFiddle demo

Upvotes: 0

Related Questions