1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
| WITH TMP AS (
SELECT TO_DATE(20151115, 'YYYYMMDD') DATE_DEBUT, TO_DATE(20151117, 'YYYYMMDD') DATE_FIN, TO_DATE(20151115, 'YYYYMMDD') DATE_INIT, 0 FLAG FROM DUAL
UNION ALL
SELECT TO_DATE(20151118, 'YYYYMMDD') DATE_DEBUT, TO_DATE(20151124, 'YYYYMMDD') DATE_FIN, NULL DATE_INIT, 1 FLAG FROM DUAL
UNION ALL
SELECT TO_DATE(20151125, 'YYYYMMDD') DATE_DEBUT, TO_DATE(20151206, 'YYYYMMDD') DATE_FIN, NULL DATE_INIT, 1 FLAG FROM DUAL
UNION ALL
SELECT TO_DATE(20160101, 'YYYYMMDD') DATE_DEBUT, TO_DATE(20160108, 'YYYYMMDD') DATE_FIN, TO_DATE(20160101, 'YYYYMMDD') DATE_INIT, 0 FLAG FROM DUAL
UNION ALL
SELECT TO_DATE(20160109, 'YYYYMMDD') DATE_DEBUT, TO_DATE(20160119, 'YYYYMMDD') DATE_FIN, NULL DATE_INIT, 1 FLAG FROM DUAL
)
SELECT
DATE_DEBUT
, DATE_FIN
, LAST_VALUE(DATE_INIT IGNORE NULLS) OVER (ORDER BY DATE_DEBUT) DATE_INIT
, FLAG
FROM TMP |
Partager