Select min start and max end for consecutive periods in SQL Server 2012+

user_1706889 0
Viewed 811

I have a table in SQL Server 2012 with events log in a format like this:

+=====+=============================+=============================+======+
| ID1 |       start_time_utc        |        end_time_utc         | ID2  |
+=====+=============================+=============================+======+
|  57 | 2018-11-11 11:00:00.0000000 | 2018-11-11 11:00:28.0012900 |   15 |
|  57 | 2018-11-11 11:00:28.0012900 | 2018-11-11 11:01:29.0543947 | 1020 |
|  57 | 2018-11-11 11:01:29.0543947 | 2018-11-11 11:02:28.1923079 |   16 |
|  57 | 2018-11-11 11:02:28.1923079 | 2018-11-11 11:04:28.3367626 |   16 |
|  57 | 2018-11-11 11:04:28.3367626 | 2018-11-11 11:05:28.5307626 | 1020 |
| 103 | 2018-11-10 20:00:00.0000000 | 2018-11-11 03:00:00.0000000 |   15 |
| 103 | 2018-11-11 03:00:00.0000000 | 2018-11-11 10:57:00.8175737 |   15 |
| 103 | 2018-11-11 10:57:00.8175737 | 2018-11-11 10:57:27.8322749 | 1017 |
| 103 | 2018-11-11 10:57:27.8322749 | 2018-11-11 11:00:00.0000000 |   15 |
| 103 | 2018-11-11 11:00:00.0000000 | 2018-11-11 11:00:31.9916890 |   15 |
+-----+-----------------------------+-----------------------------+------+

For a given ID1, end date has a matching start date of the next event. I want to partition the data by matching ID1 and ID2 columns, and select start date and end date for each partition of consecutive events. So the result should be:

+=====+=============================+=============================+======+
| ID1 |       start_time_utc        |        end_time_utc         | ID2  |
+=====+=============================+=============================+======+
|  57 | 2018-11-11 11:00:00.0000000 | 2018-11-11 11:00:28.0012900 |   15 |
|  57 | 2018-11-11 11:00:28.0012900 | 2018-11-11 11:01:29.0543947 | 1020 |
|  57 | 2018-11-11 11:01:29.0543947 | 2018-11-11 11:04:28.3367626 |   16 |
|  57 | 2018-11-11 11:04:28.3367626 | 2018-11-11 11:05:28.5307626 | 1020 |
| 103 | 2018-11-10 20:00:00.0000000 | 2018-11-11 10:57:00.8175737 |   15 |
| 103 | 2018-11-11 10:57:00.8175737 | 2018-11-11 10:57:27.8322749 | 1017 |
| 103 | 2018-11-11 10:57:27.8322749 | 2018-11-11 11:00:31.9916890 |   15 |
+-----+-----------------------------+-----------------------------+------+

I obviously can't use simple group by and don't know how to write the partition by query. Thanks for help.

gaps-and-islands partitioning sql-server
2 Answers

This is an updated version that works for dataset given and also handles cases where you have sequences of id1,id2 greater than 2 in all cases i have tested for. It is much simpler than my original answer. Replace tstGrouping with your table name. 

;with p as
(
  select
   ROW_NUMBER () over (order by id1, start_time_utc) as row_num,
   ROW_NUMBER () over (order by id1,id2, start_time_utc) as row_num2,
    *
  from
    tstgrouping x1
)
select 
  id1,
  min(start_time_utc) as start_time_utc,
  max(end_time_utc) as end_time_utc,
  id2
from p
  group by
row_num - row_num2,id1,id2
order by 
id1, start_time_utc
user_103945780 
;with base as
(
Select *
,row_number()over(partition by id1, 1d2 order by start_time_utc asc) ordstart
,row_number()over(partition by id1, id2, order by end_date_utc desc) ordend
)

Select * from 
base b1
inner join base b2 on b1.id1 = b2.id1 and b1.id2 = b2.id2 ans b1.ordstart = b2.ordend
Where b1.ordstart = 1
user_80944720
Related