1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
| CREATE TABLE Dates_Test(
Id NUMBER(30),
DateDebut DATE,
DateFin DATE);
INSERT INTO Dates_Test(
Id, DateDebut, DateFin)
SELECT 1, TO_DATE('20080715 08:00', 'YYYYMMDD HH24:MI'), TO_DATE('20080715 10:00', 'YYYYMMDD HH24:MI') FROM DUAL
UNION ALL
SELECT 2, TO_DATE('20080715 10:00', 'YYYYMMDD HH24:MI'), TO_DATE('20080715 12:00', 'YYYYMMDD HH24:MI') FROM DUAL
UNION ALL
SELECT 3, TO_DATE('20080715 14:00', 'YYYYMMDD HH24:MI'), TO_DATE('20080715 18:00', 'YYYYMMDD HH24:MI') FROM DUAL
UNION ALL
SELECT 4, TO_DATE('20080716 14:00', 'YYYYMMDD HH24:MI'), TO_DATE('20080716 17:00', 'YYYYMMDD HH24:MI') FROM DUAL
UNION ALL
SELECT 5, TO_DATE('20080716 17:00', 'YYYYMMDD HH24:MI'), TO_DATE('20080716 19:00', 'YYYYMMDD HH24:MI') FROM DUAL
;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD HH24:MI';
SELECT DateFin, DateDebutSuivante
FROM
(
SELECT D.*,
LEAD(D.DateDebut) OVER (ORDER BY DateDebut) DateDebutSuivante
FROM Dates_Test D
)
WHERE DateDebutSuivante > DateFin;
20080715 12:00 20080715 14:00
20080715 18:00 20080716 14:00 |
Partager