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
| WITH t1 AS
(
SELECT 'fr' lib, 7 code, TO_DATE('02/02/2010', 'DD/MM/YYYY') AS dtedeb,TO_DATE('06/02/2010', 'DD/MM/YYYY') AS dtefin FROM DUAL UNION ALL
SELECT 'fr' lib, 7 code, TO_DATE('06/02/2010', 'DD/MM/YYYY') AS dtedeb,TO_DATE('15/03/2011', 'DD/MM/YYYY') AS dtefin FROM DUAL UNION ALL
SELECT 'fr' lib, 7 code, TO_DATE('15/03/2011', 'DD/MM/YYYY') AS dtedeb,TO_DATE('20/04/2012', 'DD/MM/YYYY') AS dtefin FROM DUAL UNION ALL
SELECT 'fr' lib, 7 code, TO_DATE('20/04/2012', 'DD/MM/YYYY') AS dtedeb,TO_DATE('31/05/2012', 'DD/MM/YYYY') AS dtefin FROM DUAL UNION ALL
SELECT 'gb' lib, 8 code, TO_DATE('02/02/2010', 'DD/MM/YYYY') AS dtedeb,TO_DATE('06/02/2010', 'DD/MM/YYYY') AS dtefin FROM DUAL UNION ALL
SELECT 'gb' lib, 8 code, TO_DATE('06/02/2010', 'DD/MM/YYYY') AS dtedeb,TO_DATE('15/03/2010', 'DD/MM/YYYY') AS dtefin FROM DUAL UNION ALL
SELECT 'gb' lib, 8 code, TO_DATE('15/03/2011', 'DD/MM/YYYY') AS dtedeb,TO_DATE('20/04/2012', 'DD/MM/YYYY') AS dtefin FROM DUAL UNION ALL
SELECT 'gb' lib, 8 code, TO_DATE('20/04/2012', 'DD/MM/YYYY') AS dtedeb,TO_DATE('31/05/2012', 'DD/MM/YYYY') AS dtefin FROM DUAL
),
t2 AS(SELECT code, lib, dtedeb, dtefin,
CASE WHEN lag(code, 1) OVER (PARTITION BY code, lib ORDER BY dtedeb) = code
AND lag(dtefin, 1) OVER (PARTITION BY code, lib ORDER BY dtedeb) <> dtedeb THEN 1 ELSE 0 END cont
FROM t1),
t3 AS (SELECT code, lib, dtedeb, dtefin, SUM(cont) OVER (PARTITION BY code, lib ORDER BY dtedeb) grp
FROM t2
)
SELECT code, lib, MIN(dtedeb) date_debut, MAX(dtefin) date_fin
FROM t3
GROUP BY code, lib, grp
ORDER BY code, lib, date_debut
Résultat
CODE LIB DATE_DEBUT DATE_FIN
7 fr 02/02/2010 31/05/2012
8 gb 02/02/2010 15/03/2010
8 gb 15/03/2011 31/05/2012 |
Partager