Today Queens day has become King's day in Holland.But the traditional date of 30 April is left next year, when King's day is on 27 April, the birthday of our king Willem Alexander.
One thing the king of the Netherlands did not know is that this has impact on our holiday programming in Oracle software land ;-)
Some one asked me to fill a calendar with holidays, I decided to do this with an analytical query.
After 2013 the Queens day ( koninginnedag ) has become King's day, I used a case statement for it.
But when is EasterSunday ? Googled around I found oracle plsql code :
With eastersunday I can calculate Whitsun, Ascension Day.
create or replace
function eastersunday(p_year number default to_number(to_char(sysdate,'
return date is
/*
|| Jan Thuis, April 2004
|| Calculate the date of Easter Sunday
||
|| Gregorian method (any year since 1583) based on algorithm of Oudin
*/
l_eastersunday date;
l_g integer;
l_c integer;
l_d integer;
l_e integer;
l_h integer;
l_k integer;
l_p integer;
l_q integer;
l_i integer;
l_j integer;
l_x integer;
begin
l_g := mod(p_year,19);
l_c := floor(p_year/100);
l_d := l_c - floor(l_c/4);
l_e := floor((8 * l_c + 13)/25);
l_h := mod(l_d - l_e + 19 * l_g + 15 ,30);
l_k := floor(l_h / 28);
l_p := floor(29/(l_h + 1));
l_q := floor((21 - l_g)/11);
l_i := l_h - l_k * (1 - l_k * l_p * l_q);
l_j := mod((p_year + floor(p_year/4 + l_i + 2 - l_d )),7);
l_x := 28 + l_i - l_j;
l_eastersunday := to_date('0103'||p_year,'
return l_eastersunday;
END EASTERSUNDAY;
/
With this code I made this holiday query, simply it does this :
- Is it a workday then eerste_werkdag = datum.
- Is not a workday then eerste_werkdag = the next datum where werkdag = Y.
SELECT DATUM,
Werkdag,
CASE
WHEN TO_CHAR(Datum,'DD-MM') = '31-12'
AND Werkdag = 'N'
Then Datum+2
WHEN TO_CHAR(Datum,'DD-MM') = '30-12'
And Werkdag = 'N'
Then Datum+3
ELSE
CASE
WHEN WERKDAG = 'J'
THEN DATUM
ELSE Lead(Hulp Ignore Nulls) Over (Order By Datum)
END
END Eerste_Werkdag ,
OPMERKING
FROM
(SELECT DATUM,
WERKDAG,
CASE
WHEN werkdag = 'J'
THEN datum
ELSE NULL
END HULP,
opmerking
FROM
(SELECT MAIN.DATUM,
TO_CHAR(MAIN.DATUM,'day'),
CASE
WHEN trim(TO_CHAR(MAIN.DATUM,'day')) IN ('saturday', 'sunday' )
OR feestdag.datum IS NOT NULL
THEN 'N'
ELSE 'J'
END WERKDAG ,
OPMERKING
FROM
(SELECT TRUNC( to_date('01-01-'
||'&&JAAR', 'DD-MM-YYYY') + LEVEL -1) DATUM
FROM DUAL
CONNECT BY LEVEL <=
(SELECT DAYS_IN_YEAR
FROM
(SELECT TO_CHAR(TRUNC(input_date, 'YYYY'), 'YYYY') AS "YEAR",
ADD_MONTHS(TRUNC(INPUT_DATE, 'YYYY'), 12) - TRUNC(input_date, 'YYYY') AS days_in_year
FROM
(SELECT ADD_MONTHS(TRUNC(TO_DATE('&&JAAR','YYYY')) -365, +12 * level) INPUT_DATE
FROM dual
CONNECT BY level <= 20
)
) SEL
WHERE SEL.YEAR = TO_CHAR(to_date('&&JAAR','YYYY'),'YYYY')
)
) Main ,
(SELECT *
FROM
(SELECT Datum,
CASE
WHEN Opmerking ='Hemelvaartsdag'
AND TO_CHAR(Datum,'DD-MM') = '05-05'
THEN 'Hemelvaartsdag/Bevrijdingsdag'
ELSE Opmerking
END opmerking,
row_number() Over (Partition BY Datum Order By Datum ) DUBBELFEEST
FROM
( SELECT XXX_EASTERSUNDAY('&&JAAR') DATUM, '1e Paasdag' OPMERKING FROM DUAL
UNION ALL
SELECT XXX_EASTERSUNDAY('&&JAAR')+1, '2e Paasdag' FROM DUAL
UNION ALL
SELECT XXX_EASTERSUNDAY('&&JAAR')+39, 'Hemelvaartsdag' FROM DUAL
UNION ALL
SELECT XXX_EASTERSUNDAY('&&JAAR')+49, '1e Pinksterdag' FROM DUAL
UNION ALL
SELECT XXX_EASTERSUNDAY('&&JAAR')+50, '2e Pinksterdag' FROM DUAL
UNION ALL
SELECT TO_DATE('05-MAY-&&JAAR','DD-MON-YYYY'), 'Bevrijdingsdag' FROM DUAL
UNION ALL
SELECT TO_DATE('25-DEC-&&JAAR','DD-MON-YYYY'), '1e Kerstdag' FROM DUAL
UNION ALL
SELECT TO_DATE('26-DEC-&&JAAR','DD-MON-YYYY'), '2e Kerstdag' FROM dual
UNION ALL
SELECT TO_DATE('01-JAN-&&JAAR','DD-MON-YYYY'), 'Nieuwjaarsdag' FROM dual
UNION ALL
SELECT
CASE
WHEN
&&JAAR > 2013
THEN TO_DATE('27-APR-&&JAAR','DD-MON-YYYY')
ELSE TO_DATE('30-APR-&&JAAR','DD-MON-YYYY')
END ,
'Koning..dag'
FROM Dual
)
)
WHERE Dubbelfeest=1
) FEESTDAG
WHERE main.DATUM = FEESTDAG.DATUM (+)
ORDER BY main.DATUM
)
ORDER BY Datum
)
