1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| SELECT Y.ANNEE, Y.ANNEEMOIS, Y.MOISNUM,Y.JOUR, Y.JOURID, Y.JOURSEMTXT, Y.JOURSEMABREV, Y.JOURSEMNUM, Y.JOURMOISNUM, Y.JOURMOISNUM, Y.JOURANNUM, Y.SEMNUM, Y.WEEKEND
FROM
(SELECT to_char(X.ANNEEDTEDEB + (rownum - 1),'yyyy') AS ANNEE,
to_char(X.ANNEEDTEDEB + (rownum - 1),'yyyymm') AS ANNEEMOIS,
extract(month from X.ANNEEDTEDEB + (rownum - 1)) AS MOISNUM, X
.ANNEEDTEDEB + (rownum - 1) as JOUR,
to_char(X.ANNEEDTEDEB + (rownum - 1), 'yyyymmdd') as JOURID,
substr(to_char(X.ANNEEDTEDEB + (rownum - 1),'DAY'),1,8) AS JOURSEMTXT, t
o_char(X.ANNEEDTEDEB + (rownum - 1),'DY') AS JOURSEMABREV, t
o_char(X.ANNEEDTEDEB + (rownum - 1),'D') AS JOURSEMNUM,
to_char(X.ANNEEDTEDEB + (rownum - 1),'DD') AS JOURMOISNUM,
to_char(X.ANNEEDTEDEB + (rownum - 1),'DDD') AS JOURANNUM,
to_char(X.ANNEEDTEDEB + (rownum - 1),'WW')AS SEMNUM,
decode(to_char(X.ANNEEDTEDEB + (rownum - 1),'D'),6,'Week-end',7,'Week-end','Semaine') AS WEEKEND
FROM
(select add_months(trunc(sysdate,'YEAR'),-72) as ANNEEDTEDEB, trunc(add_months(sysdate,72),'year')-1 as ANNEEDTEFIN from dual ) X
connect BY X.ANNEEDTEDEB + (rownum - 1) <= X.ANNEEDTEFIN
) Y
WHERE Y.ANNEE BETWEEN '2015' AND '2020' |
Partager