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
|
with OT as (
select 1 num, to_date('01/01/1990') datefin from dual union
select 2 num, to_date('01/07/1990') from dual union
select 3 num, to_date('01/12/2000') from dual union
select 4 num, to_date('01/12/2005') from dual union
select 5 num, to_date('01/12/2005') from dual),
annee as (select distinct extract (year from datefin) annee from OT),
mois as (select '01' num,'Janvier' lettre from dual union
select '10','Octobre' from dual union
select '11','Novembre' from dual union
select '12','Décembre' from dual union
select '02','Février'from dual union
select '03','Mars'from dual union
select '04','Avril'from dual union
select '05','Mai'from dual union
select '06','Juin'from dual union
select '07','Juillet'from dual union
select '08','Août'from dual union
select '09','Septembre'from dual),
annee_mois as (select num, lettre, annee from mois, annee)
select distinct d.num||'/'||d.annee, count(ot.num)
from annee_mois d, ot
where d.num||'/'||d.annee = to_char(ot.datefin(+),'MM/YYYY')
group by d.num||'/'||d.annee
order by d.num||'/'||d.annee |
Partager