Select min start and max end for consecutive periods in SQL Server 2012+
up vote
3
down vote
favorite
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.
sql-server partitioning gaps-and-islands edited Nov 13 at 5:47
marc_s
566k12610921245
asked Nov 13 at 1:04
PiotrS
186
add a comment |
up vote
3
down vote
favorite
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.
sql-server partitioning gaps-and-islands edited Nov 13 at 5:47
marc_s
566k12610921245
asked Nov 13 at 1:04
PiotrS
186
Do you have some kind of column to check if this is next event or previous event ? I just wanted to understand if there is a field like that it would be easier. I was trying to order by Start date with the same ID1 and ID2 , but looking at your output I don't think it can be done with dates.
– Avi
Nov 13 at 5:05
@Avi: no, the only next/previous is per start and end times
– PiotrS
Nov 15 at 10:57
add a comment |
up vote
3
down vote
favorite
up vote
3
down vote
favorite
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.
sql-server partitioning gaps-and-islands edited Nov 13 at 5:47
marc_s
566k12610921245
asked Nov 13 at 1:04
PiotrS
186
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.
sql-server partitioning gaps-and-islands sql-server partitioning gaps-and-islands edited Nov 13 at 5:47
marc_s
566k12610921245
asked Nov 13 at 1:04
PiotrS
186
edited Nov 13 at 5:47
marc_s
566k12610921245
asked Nov 13 at 1:04
PiotrS
186
edited Nov 13 at 5:47
marc_s
566k12610921245
edited Nov 13 at 5:47
marc_s
566k12610921245
edited Nov 13 at 5:47
marc_s
566k12610921245
566k12610921245
asked Nov 13 at 1:04
PiotrS
186
asked Nov 13 at 1:04
PiotrS
186
asked Nov 13 at 1:04
PiotrS
186
186
Do you have some kind of column to check if this is next event or previous event ? I just wanted to understand if there is a field like that it would be easier. I was trying to order by Start date with the same ID1 and ID2 , but looking at your output I don't think it can be done with dates.
– Avi
Nov 13 at 5:05
@Avi: no, the only next/previous is per start and end times
– PiotrS
Nov 15 at 10:57
add a comment |
Do you have some kind of column to check if this is next event or previous event ? I just wanted to understand if there is a field like that it would be easier. I was trying to order by Start date with the same ID1 and ID2 , but looking at your output I don't think it can be done with dates.
– Avi
Nov 13 at 5:05
@Avi: no, the only next/previous is per start and end times
– PiotrS
Nov 15 at 10:57
Do you have some kind of column to check if this is next event or previous event ? I just wanted to understand if there is a field like that it would be easier. I was trying to order by Start date with the same ID1 and ID2 , but looking at your output I don't think it can be done with dates.
– Avi
Nov 13 at 5:05
Do you have some kind of column to check if this is next event or previous event ? I just wanted to understand if there is a field like that it would be easier. I was trying to order by Start date with the same ID1 and ID2 , but looking at your output I don't think it can be done with dates.
– Avi
Nov 13 at 5:05
@Avi: no, the only next/previous is per start and end times
– PiotrS
Nov 15 at 10:57
@Avi: no, the only next/previous is per start and end times
– PiotrS
Nov 15 at 10:57
add a comment |
2 Answers
2
active
oldest
votes
up vote
1
down vote
accepted
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
answered Nov 13 at 5:27
Daniels
813
Works great, thanks. Nice and simple. I earlier tried to overcomplicate by trying to join end time to the next start time. This approach doesn't care if end time has a matching start time, correct?
– PiotrS
Nov 15 at 19:50
1
I also overcomplicated it on first try, this doesnt really care about times it just exploits the fact that consecutive id1,id2 pairs will have the same row offsets when ordered by id1, start_time and id1,id2, start_time
– Daniels
Nov 16 at 1:04
add a comment |
up vote
0
down vote
;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
answered Nov 13 at 5:07
TonyRyan
4114
Done from my phone and untested, but I’m pretty sure this should work.
– TonyRyan
Nov 13 at 5:07
quick test says this answer filters out too many rows, only one of each id1, id2 combo is returned.
– Daniels
Nov 13 at 5:12
Good call. So you’ll have to join the cte to itself on b1.start_time = b2.end_time and compare the ids with a case statement. Might have run a while loops as I dont believe there is a limit on how many consecutive id pairs can exist. Im sure there will be, but if there’s not an accepted answer by tomorrow morning, I’ll put something together on break at work.
– TonyRyan
Nov 13 at 5:20
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
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
answered Nov 13 at 5:27
Daniels
813
Works great, thanks. Nice and simple. I earlier tried to overcomplicate by trying to join end time to the next start time. This approach doesn't care if end time has a matching start time, correct?
– PiotrS
Nov 15 at 19:50
1
I also overcomplicated it on first try, this doesnt really care about times it just exploits the fact that consecutive id1,id2 pairs will have the same row offsets when ordered by id1, start_time and id1,id2, start_time
– Daniels
Nov 16 at 1:04
add a comment |
up vote
1
down vote
accepted
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
answered Nov 13 at 5:27
Daniels
813
Works great, thanks. Nice and simple. I earlier tried to overcomplicate by trying to join end time to the next start time. This approach doesn't care if end time has a matching start time, correct?
– PiotrS
Nov 15 at 19:50
1
I also overcomplicated it on first try, this doesnt really care about times it just exploits the fact that consecutive id1,id2 pairs will have the same row offsets when ordered by id1, start_time and id1,id2, start_time
– Daniels
Nov 16 at 1:04
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
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
answered Nov 13 at 5:27
Daniels
813
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
answered Nov 13 at 5:27
Daniels
813
edited Nov 14 at 5:46
answered Nov 13 at 5:27
Daniels
813
answered Nov 13 at 5:27
Daniels
813
answered Nov 13 at 5:27
Daniels
813
813
Works great, thanks. Nice and simple. I earlier tried to overcomplicate by trying to join end time to the next start time. This approach doesn't care if end time has a matching start time, correct?
– PiotrS
Nov 15 at 19:50
1
I also overcomplicated it on first try, this doesnt really care about times it just exploits the fact that consecutive id1,id2 pairs will have the same row offsets when ordered by id1, start_time and id1,id2, start_time
– Daniels
Nov 16 at 1:04
add a comment |
Works great, thanks. Nice and simple. I earlier tried to overcomplicate by trying to join end time to the next start time. This approach doesn't care if end time has a matching start time, correct?
– PiotrS
Nov 15 at 19:50
1
I also overcomplicated it on first try, this doesnt really care about times it just exploits the fact that consecutive id1,id2 pairs will have the same row offsets when ordered by id1, start_time and id1,id2, start_time
– Daniels
Nov 16 at 1:04
Works great, thanks. Nice and simple. I earlier tried to overcomplicate by trying to join end time to the next start time. This approach doesn't care if end time has a matching start time, correct?
– PiotrS
Nov 15 at 19:50
Works great, thanks. Nice and simple. I earlier tried to overcomplicate by trying to join end time to the next start time. This approach doesn't care if end time has a matching start time, correct?
– PiotrS
Nov 15 at 19:50
1
1
I also overcomplicated it on first try, this doesnt really care about times it just exploits the fact that consecutive id1,id2 pairs will have the same row offsets when ordered by id1, start_time and id1,id2, start_time
– Daniels
Nov 16 at 1:04
I also overcomplicated it on first try, this doesnt really care about times it just exploits the fact that consecutive id1,id2 pairs will have the same row offsets when ordered by id1, start_time and id1,id2, start_time
– Daniels
Nov 16 at 1:04
add a comment |
up vote
0
down vote
;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
answered Nov 13 at 5:07
TonyRyan
4114
Done from my phone and untested, but I’m pretty sure this should work.
– TonyRyan
Nov 13 at 5:07
quick test says this answer filters out too many rows, only one of each id1, id2 combo is returned.
– Daniels
Nov 13 at 5:12
Good call. So you’ll have to join the cte to itself on b1.start_time = b2.end_time and compare the ids with a case statement. Might have run a while loops as I dont believe there is a limit on how many consecutive id pairs can exist. Im sure there will be, but if there’s not an accepted answer by tomorrow morning, I’ll put something together on break at work.
– TonyRyan
Nov 13 at 5:20
add a comment |
up vote
0
down vote
;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
answered Nov 13 at 5:07
TonyRyan
4114
Done from my phone and untested, but I’m pretty sure this should work.
– TonyRyan
Nov 13 at 5:07
quick test says this answer filters out too many rows, only one of each id1, id2 combo is returned.
– Daniels
Nov 13 at 5:12
Good call. So you’ll have to join the cte to itself on b1.start_time = b2.end_time and compare the ids with a case statement. Might have run a while loops as I dont believe there is a limit on how many consecutive id pairs can exist. Im sure there will be, but if there’s not an accepted answer by tomorrow morning, I’ll put something together on break at work.
– TonyRyan
Nov 13 at 5:20
add a comment |
up vote
0
down vote
up vote
0
down vote
;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
answered Nov 13 at 5:07
TonyRyan
4114
;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
answered Nov 13 at 5:07
TonyRyan
4114
answered Nov 13 at 5:07
TonyRyan
4114
answered Nov 13 at 5:07
TonyRyan
4114
answered Nov 13 at 5:07
TonyRyan
4114
4114
Done from my phone and untested, but I’m pretty sure this should work.
– TonyRyan
Nov 13 at 5:07
quick test says this answer filters out too many rows, only one of each id1, id2 combo is returned.
– Daniels
Nov 13 at 5:12
Good call. So you’ll have to join the cte to itself on b1.start_time = b2.end_time and compare the ids with a case statement. Might have run a while loops as I dont believe there is a limit on how many consecutive id pairs can exist. Im sure there will be, but if there’s not an accepted answer by tomorrow morning, I’ll put something together on break at work.
– TonyRyan
Nov 13 at 5:20
add a comment |
Done from my phone and untested, but I’m pretty sure this should work.
– TonyRyan
Nov 13 at 5:07
quick test says this answer filters out too many rows, only one of each id1, id2 combo is returned.
– Daniels
Nov 13 at 5:12
Good call. So you’ll have to join the cte to itself on b1.start_time = b2.end_time and compare the ids with a case statement. Might have run a while loops as I dont believe there is a limit on how many consecutive id pairs can exist. Im sure there will be, but if there’s not an accepted answer by tomorrow morning, I’ll put something together on break at work.
– TonyRyan
Nov 13 at 5:20
Done from my phone and untested, but I’m pretty sure this should work.
– TonyRyan
Nov 13 at 5:07
Done from my phone and untested, but I’m pretty sure this should work.
– TonyRyan
Nov 13 at 5:07
quick test says this answer filters out too many rows, only one of each id1, id2 combo is returned.
– Daniels
Nov 13 at 5:12
quick test says this answer filters out too many rows, only one of each id1, id2 combo is returned.
– Daniels
Nov 13 at 5:12
Good call. So you’ll have to join the cte to itself on b1.start_time = b2.end_time and compare the ids with a case statement. Might have run a while loops as I dont believe there is a limit on how many consecutive id pairs can exist. Im sure there will be, but if there’s not an accepted answer by tomorrow morning, I’ll put something together on break at work.
– TonyRyan
Nov 13 at 5:20
Good call. So you’ll have to join the cte to itself on b1.start_time = b2.end_time and compare the ids with a case statement. Might have run a while loops as I dont believe there is a limit on how many consecutive id pairs can exist. Im sure there will be, but if there’s not an accepted answer by tomorrow morning, I’ll put something together on break at work.
– TonyRyan
Nov 13 at 5:20
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53272295%2fselect-min-start-and-max-end-for-consecutive-periods-in-sql-server-2012%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Do you have some kind of column to check if this is next event or previous event ? I just wanted to understand if there is a field like that it would be easier. I was trying to order by Start date with the same ID1 and ID2 , but looking at your output I don't think it can be done with dates.
– Avi
Nov 13 at 5:05
@Avi: no, the only next/previous is per start and end times
– PiotrS
Nov 15 at 10:57