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 |
