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
| With MaTable as
(
select 1 as id, date '2010-01-02' as debut, date '2010-02-02' as fin, 'MENSUEL' as periode from dual union all
select 1 , date '2010-02-03' , date '2010-03-03' , 'MENSUEL' from dual union all
select 1 , date '2010-03-04' , date '2010-06-04' , 'TRIMESTRIEL' from dual union all
select 1 , date '2010-06-05' , date '2010-07-05' , 'MENSUEL' from dual union all
select 1 , date '2010-07-05' , date '2011-07-05' , 'ANNUEL' from dual union all
select 1 , date '2011-07-05' , date '2011-08-05' , 'MENSUEL' from dual union all
select 1 , date '2011-08-06' , date '2011-09-06' , 'MENSUEL' from dual
)
, SR as
(
select id, debut, fin, periode,
row_number() over(partition by id order by debut asc) -
row_number() over(partition by id, periode order by debut asc) as grp
from MaTable
)
select id, min(debut) as debut, max(fin) as fin, periode
from SR
group by id, periode, grp
order by id, debut;
ID DEBUT FIN PERIODE
---------- ---------- ---------- -----------
1 02/01/2010 03/03/2010 MENSUEL
1 04/03/2010 04/06/2010 TRIMESTRIEL
1 05/06/2010 05/07/2010 MENSUEL
1 05/07/2010 05/07/2011 ANNUEL
1 05/07/2011 06/09/2011 MENSUEL |
Partager