Tuesday, April 30, 2013

King of the Netherlands and Oracle software and holidays.

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,'RRRR'))) 
  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,'DDMMYYYY') + l_x -1; 
  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
  )