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
| with MaTable as
(
select 1 as rid, date '2013-06-15' as dt_deb, date '2013-08-25' as dt_fin from dual union all
select 2 , date '2012-01-13' , date '2012-01-27' from dual union all
select 3 , date '2012-03-13' , date '2012-04-10' from dual union all
select 4 , date '2012-06-15' , date '2012-07-15' from dual union all
select 5 , date '2013-12-02' , date '2014-02-01' from dual
)
, CTE (rid, dt_deb, dt_fin, lvl) as
(
select rid, dt_deb, least(dt_fin, last_day(dt_deb)), 1 as lvl
from MaTable
union all
select ct.rid
, trunc(add_months(mt.dt_deb, ct.lvl), 'mm')
, least(mt.dt_fin, last_day(add_months(mt.dt_fin, ct.lvl - months_between(trunc(mt.dt_fin, 'mm'), trunc(mt.dt_deb, 'mm')))))
, ct.lvl + 1
from CTE ct
join MaTable mt
on mt.rid = ct.rid
where ct.lvl <= months_between(trunc(mt.dt_fin, 'mm'), trunc(mt.dt_deb, 'mm'))
) cycle lvl set is_cycle to '1' default '0'
select rid, to_char(dt_deb, 'yyyy-mm-dd') as dt_deb, to_char(dt_fin, 'yyyy-mm-dd') as dt_fin
from CTE
order by 1,2;
RID DT_DEB DT_FIN
--- ---------- ----------
1 2013-06-15 2013-06-30
1 2013-07-01 2013-07-31
1 2013-08-01 2013-08-25
2 2012-01-13 2012-01-27
3 2012-03-13 2012-03-31
3 2012-04-01 2012-04-10
4 2012-06-15 2012-06-30
4 2012-07-01 2012-07-15
5 2013-12-02 2013-12-31
5 2014-01-01 2014-01-31
5 2014-02-01 2014-02-01 |
Partager