Select min start and max end for consecutive periods in SQL Server 2012+
3
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 '18 at 5:47
marc_s
571k12811031252
asked Nov 13 '18 at 1:04
PiotrSPiotrS
317
add a comment |
3
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 '18 at 5:47
marc_s
571k12811031252
asked Nov 13 '18 at 1:04
PiotrSPiotrS
317
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 '18 at 5:05
@Avi: no, the only next/previous is per start and end times
– PiotrS
Nov 15 '18 at 10:57
add a comment |
3
3
3
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 '18 at 5:47
marc_s
571k12811031252
asked Nov 13 '18 at 1:04
PiotrSPiotrS
317
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 '18 at 5:47
marc_s
571k12811031252
asked Nov 13 '18 at 1:04
PiotrSPiotrS
317
edited Nov 13 '18 at 5:47
marc_s
571k12811031252
asked Nov 13 '18 at 1:04
PiotrSPiotrS
317
edited Nov 13 '18 at 5:47
marc_s
571k12811031252
edited Nov 13 '18 at 5:47
marc_s
571k12811031252
edited Nov 13 '18 at 5:47
marc_s
571k12811031252
571k12811031252
asked Nov 13 '18 at 1:04
PiotrSPiotrS
317
asked Nov 13 '18 at 1:04
PiotrSPiotrS
317
asked Nov 13 '18 at 1:04
PiotrSPiotrS
317
317
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 '18 at 5:05
@Avi: no, the only next/previous is per start and end times
– PiotrS
Nov 15 '18 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 '18 at 5:05
@Avi: no, the only next/previous is per start and end times
– PiotrS
Nov 15 '18 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 '18 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 '18 at 5:05
@Avi: no, the only next/previous is per start and end times
– PiotrS
Nov 15 '18 at 10:57
@Avi: no, the only next/previous is per start and end times
– PiotrS
Nov 15 '18 at 10:57
add a comment |
2 Answers
2
active
oldest
votes
1
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 '18 at 5:27
DanielsDaniels
1064
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 '18 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 '18 at 1:04
add a comment |
0
;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 '18 at 5:07
TonyRyanTonyRyan
4114
Done from my phone and untested, but I’m pretty sure this should work.
– TonyRyan
Nov 13 '18 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 '18 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 '18 at 5:20
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
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 '18 at 5:27
DanielsDaniels
1064
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 '18 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 '18 at 1:04
add a comment |
1
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 '18 at 5:27
DanielsDaniels
1064
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 '18 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 '18 at 1:04
add a comment |
1
1
1
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 '18 at 5:27
DanielsDaniels
1064
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 '18 at 5:27
DanielsDaniels
1064
edited Nov 14 '18 at 5:46
answered Nov 13 '18 at 5:27
DanielsDaniels
1064
answered Nov 13 '18 at 5:27
DanielsDaniels
1064
answered Nov 13 '18 at 5:27
DanielsDaniels
1064
1064
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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 at 1:04
add a comment |
0
;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 '18 at 5:07
TonyRyanTonyRyan
4114
Done from my phone and untested, but I’m pretty sure this should work.
– TonyRyan
Nov 13 '18 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 '18 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 '18 at 5:20
add a comment |
0
;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 '18 at 5:07
TonyRyanTonyRyan
4114
Done from my phone and untested, but I’m pretty sure this should work.
– TonyRyan
Nov 13 '18 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 '18 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 '18 at 5:20
add a comment |
0
0
0
;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 '18 at 5:07
TonyRyanTonyRyan
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 '18 at 5:07
TonyRyanTonyRyan
4114
answered Nov 13 '18 at 5:07
TonyRyanTonyRyan
4114
answered Nov 13 '18 at 5:07
TonyRyanTonyRyan
4114
answered Nov 13 '18 at 5:07
TonyRyanTonyRyan
4114
4114
Done from my phone and untested, but I’m pretty sure this should work.
– TonyRyan
Nov 13 '18 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 '18 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 '18 at 5:20
add a comment |
Done from my phone and untested, but I’m pretty sure this should work.
– TonyRyan
Nov 13 '18 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 '18 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 '18 at 5:20
Done from my phone and untested, but I’m pretty sure this should work.
– TonyRyan
Nov 13 '18 at 5:07
Done from my phone and untested, but I’m pretty sure this should work.
– TonyRyan
Nov 13 '18 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 '18 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 '18 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 '18 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 '18 at 5:20
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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 '18 at 5:05
@Avi: no, the only next/previous is per start and end times
– PiotrS
Nov 15 '18 at 10:57