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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
|
WITH Periodes AS
(SELECT 'toto' nom,
to_date('11/03/2013','DD/MM/YYYY') datDeb,
to_date('23/03/2013','DD/MM/YYYY') datFin
FROM dual
UNION
SELECT 'toto' ,
to_date('24/04/2013','DD/MM/YYYY'),
to_date('29/04/2013','DD/MM/YYYY')
FROM dual
UNION
SELECT 'toto' ,
to_date('30/04/2013','DD/MM/YYYY'),
to_date('06/05/2013','DD/MM/YYYY')
FROM dual
UNION
SELECT 'toto' ,
to_date('07/05/2013','DD/MM/YYYY'),
to_date('06/06/2013','DD/MM/YYYY')
FROM dual
UNION
SELECT 'toto' ,
to_date('17/07/2013','DD/MM/YYYY'),
to_date('19/07/2013','DD/MM/YYYY')
FROM dual
UNION
SELECT 'toto' ,
to_date('20/07/2013','DD/MM/YYYY'),
to_date('08/09/2013','DD/MM/YYYY')
FROM dual
),
RuptPeriod AS
(SELECT nom,
datDeb,
datFin,
lead(datDeb)over (partition BY nom order by datDeb)datDebSuiv,
lag(datFin)over (partition BY nom order by datDeb) datFinPrec,
CASE
WHEN datDeb-1= lag(datFin)over (partition BY nom order by datDeb)
/*date de debut suivante-1 est egale
** à la date de fin precedente => tjs meme period
*/
THEN 0
/*pour le 1ere periode datFinPrec est
** null et on passe au else
*/
ELSE 1
/*
**debut nouvelle periode
*/
END DebPeriod
FROM periodes
),
DatIntervPeriod AS
(SELECT nom,
datDeb,
datFin,
datDebSuiv,
debPeriod,
SUM(debPeriod) over (partition BY nom order by datDeb) numPeriod
/*la somme(1 si debut periode 0 sinon)
** donne numPeriod : numero intervalle contigü
*/
FROM RuptPeriod
)
SELECT nom,
numPeriod,
/* MIN(datDeb) et MAX(datFin) sur partition nom et numPeriod
** donne debut et fin des intervalles contigüs
*/
MIN(datDeb)debIntervPeriod,
MAX(datFin) datFinIntervPeriod
FROM DatIntervPeriod
GROUP BY nom,
numPeriod
ORDER BY nom,numPeriod |
Partager