i have a table with two columns event_start and event_end :
CREATE TABLE MYBATCHTAB
(
EVENT_START DATE NOT NULL,
EVENT_END DATE NOT NULL
);
and my data is :
Insert into MYBATCHTAB
(EVENT_START, EVENT_END)
Values
(TO_DATE('08/12/2013 22:45:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/12/2013 23:55:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MYBATCHTAB
(EVENT_START, EVENT_END)
Values
(TO_DATE('08/12/2013 15:30:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/12/2013 17:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MYBATCHTAB
(EVENT_START, EVENT_END)
Values
(TO_DATE('08/12/2013 16:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/12/2013 17:30:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MYBATCHTAB
(EVENT_START, EVENT_END)
Values
(TO_DATE('08/12/2013 20:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/12/2013 22:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
| Event Start | Event End |
|---|
| 08/12/2013 15:30:00 | 08/12/2013 17:00:00 |
| 08/12/2013 16:00:00 | 08/12/2013 17:30:00 |
| 08/12/2013 20:00:00' | 08/12/2013 22:00:00 |
| 08/12/2013 22:45:00 | 08/12/2013 23:55:00 |
and i want to find the first whole start - end period in this example start : 15:30 - end 17:30 (merging record 1&2 )
but not the third one for example not 15.30 - 22:00 or not 15.30 23:55 because there are gaps between end dates.
how can i do this using lead&lag ?
I'm not sure if this is the best approach