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
| with cte_my_data (id, cd, dt_deb, dt_fin, qt) as
(
select 1, 6, date '2010-01-01', date '2010-12-31', 10 from dual union all
select 2, 6, date '2012-01-01', date '2012-12-31', 12 from dual union all
select 3, 6, date '2014-01-01', date '2016-12-31', 15 from dual union all
select 4, 7, date '2010-01-01', date '2010-12-31', 10 from dual union all
select 5, 7, date '2012-01-01', date '2012-12-31', 12 from dual
)
, cte_bornes (cd, dt_deb_min, dt_fin_max) as
(
select cd, min(dt_deb), max(dt_fin)
from cte_my_data
group by cd
)
, cte_calendar (cd, dt) as
(
select cd, dt_deb_min + level - 1
from cte_bornes
connect by level <= dt_fin_max - dt_deb_min
and prior cd = cd
and prior dbms_random.value is not null
)
, cte_dates_manquantes as
(
select cal.cd
, cal.dt
, cal.dt - row_number() over(partition by cal.cd order by cal.dt asc) as grp
from cte_calendar cal
where not exists (select null
from cte_my_data cmd
where cal.dt >= cmd.dt_deb
and cal.dt <= cmd.dt_fin
and cal.cd = cmd.cd)
)
select 0 as id
, cd
, min(dt) as dt_deb
, max(dt) as dt_fin
, 0 as qte
from cte_dates_manquantes
group by cd, grp
union all
select id, cd, dt_deb, dt_fin, qt
from cte_my_data
order by cd asc, dt_deb asc;
ID CD DT_DEB DT_FIN QTE
---------- ---------- ---------- ---------- --------
1 6 2010-01-01 2010-12-31 10
0 6 2011-01-01 2011-12-31 0
2 6 2012-01-01 2012-12-31 12
0 6 2013-01-01 2013-12-31 0
3 6 2014-01-01 2016-12-31 15
4 7 2010-01-01 2010-12-31 10
0 7 2011-01-01 2011-12-31 0
5 7 2012-01-01 2012-12-31 12 |
Partager