Was wondering if you could help me on a SQL problem I’m having.
I have a set of records of events where each event has a start time and end time. No event has the same start time, and the end time for each event is greater than or equal to the preceding event.
What I want to be able to do is pull out the set of records that are not overlapping – where the start time for an event is not between the start/end for any other event. In the example below, the start time for B/C/D are in-between A’s start and end, so I want to keep A, and get rid of B,C,D. Then start over again, keeping E, and get rid of F, then start over and keep G, getting rid of H/I, etc.
Seems like this requires a self join but I can’t figure out the criteria for that would identify the records this way. Any ideas?
Event | Start | End A 1 10 (keep) B 2 11 (discard because start time of 2 < 10) C 5 15 (discard because start time of 5 < 10) D 9 12 (discard because start time of 9 < 10) E 10 13 (keep - this starts a new set because it’s the first record after A with start >= A end) F 11 20 (discard because 11 < 13) G 14 22 (keep - this starts a new set because it’s the first record after E with start >= E) H 15 22 (discard because 15 < 22) I 17 27 (discard because 17 < 22) J 22 27 (keep - this starts a new set because it’s the first record after G with start >= G)