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 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
| -- creation d'un calendrier à partir d'une date de début DTDEB
-- et pour un nombre de jours paramétré
with T0(DTDEB) as
(select '2025-01-01'
from SYSIBM.SYSDUMMY1
)
-- la fonction DAYNAME est rarement implémentée, d'où T1 "en dur"
, T1(T1JNUM, T1JNOM) as
(select 1, 'lundi' from SYSIBM.SYSDUMMY1 union all
select 2, 'mardi' from SYSIBM.SYSDUMMY1 union all
select 3, 'mercredi' from SYSIBM.SYSDUMMY1 union all
select 4, 'jeudi' from SYSIBM.SYSDUMMY1 union all
select 5, 'vendredi' from SYSIBM.SYSDUMMY1 union all
select 6, 'samedi' from SYSIBM.SYSDUMMY1 union all
select 7, 'dimanche' from SYSIBM.SYSDUMMY1
)
, T2(T2DTE, T2JNUM, T2JNOM, T2QQQ, T2SEM, T2OUV, T2SEQ) as
(select cast(DTDEB as date)
, dayofweek_iso(DTDEB)
, T1JNOM
, days(DTDEB)-days(substr(DTDEB, 01, 05) !! '01-01') + 1
, week_iso(DTDEB)
, case when dayofweek_iso(DTDEB) = 7 then 0
when substr(DTDEB, 06, 05) = '01-01' then 0
when substr(DTDEB, 06, 05) = '05-01' then 0
when substr(DTDEB, 06, 05) = '05-08' then 0
when substr(DTDEB, 06, 05) = '07-14' then 0
when substr(DTDEB, 06, 05) = '08-15' then 0
when substr(DTDEB, 06, 05) = '11-01' then 0
when substr(DTDEB, 06, 05) = '11-11' then 0
when substr(DTDEB, 06, 05) = '12-25' then 0
else 1
end
, cast(1 as smallint)
from T0
inner join T1
on T1JNUM=DAYOFWEEK_ISO(DTDEB)
union all
select T2DTE + 1 days
, DAYOFWEEK_ISO(T2DTE + 1 days)
, T1JNOM
, days(T2DTE + 1 days)
- days(substr(char(T2DTE), 01, 05) !! '01-01') + 1
, week_iso(T2DTE + 1 days)
, case when dayofweek_iso(T2DTE + 1 days) = 7 then 0
when substr(char(T2DTE+1 days), 06, 05) ='01-01'
then 0
when substr(char(T2DTE+1 days), 06, 05) ='05-01'
then 0
when substr(char(T2DTE+1 days), 06, 05) ='05-08'
then 0
when substr(char(T2DTE+1 days), 06, 05) ='07-14'
then 0
when substr(char(T2DTE+1 days), 06, 05) ='08-15'
then 0
when substr(char(T2DTE+1 days), 06, 05) ='11-01'
then 0
when substr(char(T2DTE+1 days), 06, 05) ='11-11'
then 0
when substr(char(T2DTE+1 days), 06, 05) ='12-25'
then 0
else 1
end
, T2SEQ + 1
from T2
inner join T1
on T1JNUM=DAYOFWEEK_ISO(T2DTE + 1 days)
where T2SEQ < 366
)
select T2DTE, T2JNUM, T2JNOM, T2QQQ, T2SEM, T2OUV
from T2
order by T2DTE
; |