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
| With Table1 (Produit, Date_debut, Date_fin, Prix) as
(
select 'A', to_date('01.01.2008', 'dd.mm.yyyy'), to_date('31.03.2008', 'dd.mm.yyyy'), 15 from dual union all
select 'A', to_date('01.04.2008', 'dd.mm.yyyy'), to_date('30.06.2008', 'dd.mm.yyyy'), 17 from dual union all
select 'B', to_date('01.07.2008', 'dd.mm.yyyy'), to_date('31.12.2008', 'dd.mm.yyyy'), 16 from dual
)
, Table2 (Code, Date_Debut, Date_Fin, Reduction) as
(
select 1, to_date('01.01.2007', 'dd.mm.yyyy'), to_date('31.07.2008', 'dd.mm.yyyy'), 5 from dual union all
select 1, to_date('01.08.2008', 'dd.mm.yyyy'), to_date('31.08.2008', 'dd.mm.yyyy'), 4 from dual union all
select 2, to_date('01.09.2008', 'dd.mm.yyyy'), to_date('31.12.2009', 'dd.mm.yyyy'), 5 from dual
)
, t1 as
(
select Produit, Date_debut, Date_fin, Prix, rownum as rn from Table1
)
, t2 as
(
select Code, Date_Debut, Date_Fin, Reduction, rownum as rn from Table2
)
select min(cal.cal_jour) as Date_Debut
, max(cal.cal_jour) as Date_Fin
, t1.Produit, t2.Code
from v_calendrier cal
left join t1 on cal.cal_jour between t1.Date_debut and t1.Date_fin
left join t2 on cal.cal_jour between t2.Date_debut and t2.Date_fin
where t1.Produit is not null
or t2.Code is not null
group by t1.Produit, t2.Code, t1.rn
order by min(cal.cal_jour) asc;
DATE_DEBUT DATE_FIN PRODUIT CODE
---------- ---------- ------- ----
01.01.2007 31.12.2007 - 1
01.01.2008 31.03.2008 A 1
01.04.2008 30.06.2008 A 1
01.07.2008 31.08.2008 B 1
01.09.2008 31.12.2008 B 2
01.01.2009 31.12.2009 - 2 |
