Date list with US holidays plus everyday from previous Saturday labeled

1
votes

I would like to create a list of dates starting from January 1 three years prior to today up to December 31, 2 years into the future. In a second column I would like to label Christmas(XMAS), New Year's Eve(NYE), New Year's Day(NYD), Memorial Day(MEM), July 4th(JUL4), Labor Day(LABOR). For the latter 3 holidays I need to label not only the holiday date, but also every day from the previous Saturday, so as to label the whole long weekend. Otherwise label the date with the weekday name.

    SELECT STAYDATE, 
    CASE 
    WHEN TO_CHAR(STAYDATE,'MM')=12 AND TO_CHAR(STAYDATE,'DD')=25 THEN 'XMAS' 
    WHEN TO_CHAR(STAYDATE,'MM')=12 AND TO_CHAR(STAYDATE,'DD')=31 THEN 'NYE'
    WHEN TO_CHAR(STAYDATE,'MM')=01 AND TO_CHAR(STAYDATE,'DD')=01 THEN 'NYD'
    WHEN TO_CHAR(STAYDATE,'MM')=7 AND TO_CHAR(STAYDATE,'DD')=04 THEN 'JUL4'
    ELSE
    TO_CHAR(STAYDATE,'dy') END DAYLABEL
    FROM (
    SELECT TRUNC (add_months(sysdate,24) - ROWNUM) STAYDATE
    FROM DUAL CONNECT BY ROWNUM < 1000)
2
sqloracledate
So whats the issue you are facing ? – XING
Example Labor Day (1st of May): Do you consider it as "long weekend" only if 1st of May falls on Friday or also when it falls on Monday? – Wernfried Domscheit

2 Answers

0
votes

SQL Fiddle

Query 1:

SELECT DT,
       CASE
       WHEN     EXTRACT( MONTH FROM DT ) = 12
            AND EXTRACT( DAY   FROM DT ) = 25
       THEN 'XMAS'
       WHEN     EXTRACT( MONTH FROM DT ) = 12
            AND EXTRACT( DAY   FROM DT ) = 31
       THEN 'NYE'
       WHEN     EXTRACT( MONTH FROM DT ) =  1
            AND EXTRACT( DAY   FROM DT ) =  1
       THEN 'NYD'
       WHEN     EXTRACT( MONTH FROM DT ) =  7
            AND DT BETWEEN NEXT_DAY( TRUNC( DT, 'MM' ) - 4, 'SATURDAY' )
                   AND     TRUNC( DT, 'MM' ) + 3
       THEN 'JUL4'
       WHEN     EXTRACT( MONTH FROM DT ) =  9
            AND DT BETWEEN NEXT_DAY( TRUNC( DT, 'MM' ) - 1, 'MONDAY' ) - 2
                   AND     NEXT_DAY( TRUNC( DT, 'MM' ) - 1, 'MONDAY' )
       THEN 'LABOR'
       WHEN     EXTRACT( MONTH FROM DT ) =  5
            AND DT BETWEEN NEXT_DAY( TRUNC( LAST_DAY( DT ) ) - 7, 'MONDAY' ) - 2
                   AND     NEXT_DAY( TRUNC( LAST_DAY( DT ) ) - 7, 'MONDAY' )
       THEN 'MEM'
       ELSE TO_CHAR( DT, 'DY' )
       END AS day
FROM   (
  SELECT ADD_MONTHS( TRUNC( SYSDATE, 'YYYY' ), -36 ) + LEVEL - 1 AS DT
  FROM   DUAL
  CONNECT BY
         ADD_MONTHS( TRUNC( SYSDATE, 'YYYY' ), -36 ) + LEVEL - 1
           <  ADD_MONTHS( TRUNC( SYSDATE, 'YYYY' ), +24 )
)

Results:

|                   DT |   DAY |
|----------------------|-------|
| 2015-01-01T00:00:00Z |   NYD |
| 2015-01-02T00:00:00Z |   FRI |
| 2015-01-03T00:00:00Z |   SAT |
| 2015-01-04T00:00:00Z |   SUN |
| 2015-01-05T00:00:00Z |   MON |
| 2015-01-06T00:00:00Z |   TUE |
| 2015-01-07T00:00:00Z |   WED |
| 2015-01-08T00:00:00Z |   THU |
| 2015-01-09T00:00:00Z |   FRI |
| 2015-01-10T00:00:00Z |   SAT |
| 2015-01-11T00:00:00Z |   SUN |
| 2015-01-12T00:00:00Z |   MON |
...
| 2015-05-21T00:00:00Z |   THU |
| 2015-05-22T00:00:00Z |   FRI |
| 2015-05-23T00:00:00Z |   MEM |
| 2015-05-24T00:00:00Z |   MEM |
| 2015-05-25T00:00:00Z |   MEM |
| 2015-05-26T00:00:00Z |   TUE |
| 2015-05-27T00:00:00Z |   WED |
...
| 2015-07-02T00:00:00Z |   THU |
| 2015-07-03T00:00:00Z |   FRI |
| 2015-07-04T00:00:00Z |  JUL4 |
| 2015-07-05T00:00:00Z |   SUN |
| 2015-07-06T00:00:00Z |   MON |
...
| 2015-09-03T00:00:00Z |   THU |
| 2015-09-04T00:00:00Z |   FRI |
| 2015-09-05T00:00:00Z | LABOR |
| 2015-09-06T00:00:00Z | LABOR |
| 2015-09-07T00:00:00Z | LABOR |
| 2015-09-08T00:00:00Z |   TUE |
| 2015-09-09T00:00:00Z |   WED |
...
| 2015-12-23T00:00:00Z |   WED |
| 2015-12-24T00:00:00Z |   THU |
| 2015-12-25T00:00:00Z |  XMAS |
| 2015-12-26T00:00:00Z |   SAT |
| 2015-12-27T00:00:00Z |   SUN |
| 2015-12-28T00:00:00Z |   MON |
| 2015-12-29T00:00:00Z |   TUE |
| 2015-12-30T00:00:00Z |   WED |
| 2015-12-31T00:00:00Z |   NYE |
| 2016-01-01T00:00:00Z |   NYD |
| 2016-01-02T00:00:00Z |   SAT |
| 2016-01-03T00:00:00Z |   SUN |
...
| 2016-05-26T00:00:00Z |   THU |
| 2016-05-27T00:00:00Z |   FRI |
| 2016-05-28T00:00:00Z |   MEM |
| 2016-05-29T00:00:00Z |   MEM |
| 2016-05-30T00:00:00Z |   MEM |
| 2016-05-31T00:00:00Z |   TUE |
| 2016-06-01T00:00:00Z |   WED |
...
| 2016-06-30T00:00:00Z |   THU |
| 2016-07-01T00:00:00Z |   FRI |
| 2016-07-02T00:00:00Z |  JUL4 |
| 2016-07-03T00:00:00Z |  JUL4 |
| 2016-07-04T00:00:00Z |  JUL4 |
| 2016-07-05T00:00:00Z |   TUE |
| 2016-07-06T00:00:00Z |   WED |
...
| 2016-09-01T00:00:00Z |   THU |
| 2016-09-02T00:00:00Z |   FRI |
| 2016-09-03T00:00:00Z | LABOR |
| 2016-09-04T00:00:00Z | LABOR |
| 2016-09-05T00:00:00Z | LABOR |
| 2016-09-06T00:00:00Z |   TUE |
| 2016-09-07T00:00:00Z |   WED |
...
| 2016-12-23T00:00:00Z |   FRI |
| 2016-12-24T00:00:00Z |   SAT |
| 2016-12-25T00:00:00Z |  XMAS |
| 2016-12-26T00:00:00Z |   MON |
| 2016-12-27T00:00:00Z |   TUE |
| 2016-12-28T00:00:00Z |   WED |
| 2016-12-29T00:00:00Z |   THU |
| 2016-12-30T00:00:00Z |   FRI |
| 2016-12-31T00:00:00Z |   NYE |
| 2017-01-01T00:00:00Z |   NYD |
| 2017-01-02T00:00:00Z |   MON |
| 2017-01-03T00:00:00Z |   TUE |
...
| 2017-05-25T00:00:00Z |   THU |
| 2017-05-26T00:00:00Z |   FRI |
| 2017-05-27T00:00:00Z |   MEM |
| 2017-05-28T00:00:00Z |   MEM |
| 2017-05-29T00:00:00Z |   MEM |
| 2017-05-30T00:00:00Z |   TUE |
| 2017-05-31T00:00:00Z |   WED |
...
and so on...
...
| 2019-12-23T00:00:00Z |   MON |
| 2019-12-24T00:00:00Z |   TUE |
| 2019-12-25T00:00:00Z |  XMAS |
| 2019-12-26T00:00:00Z |   THU |
| 2019-12-27T00:00:00Z |   FRI |
| 2019-12-28T00:00:00Z |   SAT |
| 2019-12-29T00:00:00Z |   SUN |
| 2019-12-30T00:00:00Z |   MON |
| 2019-12-31T00:00:00Z |   NYE |
0
votes

You could also utilize the SCHEDULE objects. In my opinion the Calendaring Syntax provides more flexibility than native DATE functions.

BEGIN
    DBMS_SCHEDULER.CREATE_SCHEDULE('NEW_YEARS_DAY', repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYDATE=0101');
    DBMS_SCHEDULER.CREATE_SCHEDULE('MARTIN_LUTHER_KING_DAY', repeat_interval => 'FREQ=MONTHLY;BYMONTH=JAN;BYDAY=3 MON', comments => 'Third Monday of January');
    DBMS_SCHEDULER.CREATE_SCHEDULE('WASHINGTONS_BIRTHDAY', repeat_interval => 'FREQ=MONTHLY;BYMONTH=FEB;BYDAY=3 MON', comments => 'Third Monday of February');
    DBMS_SCHEDULER.CREATE_SCHEDULE('LABOR_DAY', repeat_interval => 'FREQ=YEARLY;BYDATE=0501');
    DBMS_SCHEDULER.CREATE_SCHEDULE('MEMORIAL_DAY', repeat_interval => 'FREQ=MONTHLY;BYMONTH=MAY;BYDAY=-1 MON', comments => 'Last Monday of May');
    DBMS_SCHEDULER.CREATE_SCHEDULE('INDEPENDENCE_DAY', repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYDATE=0704');
    DBMS_SCHEDULER.CREATE_SCHEDULE('CHRISTMAS_DAY', repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYDATE=1225');
    DBMS_SCHEDULER.CREATE_SCHEDULE('NEW_YEARS_EVE', repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYDATE=1231');   
END;

Then you would use the schedules similar to this:

DECLARE
   next_run_date TIMESTAMP;   
BEGIN

    FOR aSchedule IN (SELECT * FROM USER_SCHEDULER_SCHEDULES WHERE SCHEDULE_NAME IN ('CHRISTMAS_DAY','NEW_YEARS_EVE','NEW_YEARS_DAY','INDEPENDENCE_DAY','MEMORIAL_DAY','LABOR_DAY') ) LOOP
        next_run_date := ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -36);
        LOOP    
            DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(aSchedule.REPEAT_INTERVAL, NULL, next_run_date, next_run_date);
            EXIT WHEN next_run_date > ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12)-1;
            DBMS_OUTPUT.PUT_LINE( TO_CHAR(next_run_date, 'YYYY-MM-DD ')||aSchedule.SCHEDULE_NAME );
            IF TO_CHAR(next_run_date, 'Dy', 'nls_date_language=american') = 'Fri' AND aSchedule.SCHEDULE_NAME IN ('INDEPENDENCE_DAY','MEMORIAL_DAY','LABOR_DAY') THEN
                DBMS_OUTPUT.PUT_LINE( TO_CHAR(next_run_date+1, 'YYYY-MM-DD ')||aSchedule.SCHEDULE_NAME );
                DBMS_OUTPUT.PUT_LINE( TO_CHAR(next_run_date+2, 'YYYY-MM-DD ')||aSchedule.SCHEDULE_NAME );
            END IF;
        END LOOP;
    END LOOP;

END;

2015-12-25 CHRISTMAS_DAY
2016-12-25 CHRISTMAS_DAY
2017-12-25 CHRISTMAS_DAY
2018-12-25 CHRISTMAS_DAY
2015-07-04 INDEPENDENCE_DAY
2016-07-04 INDEPENDENCE_DAY
2017-07-04 INDEPENDENCE_DAY
2018-07-04 INDEPENDENCE_DAY
2015-05-01 LABOR_DAY
2015-05-02 LABOR_DAY
2015-05-03 LABOR_DAY
2016-05-01 LABOR_DAY
2017-05-01 LABOR_DAY
2018-05-01 LABOR_DAY
2015-05-25 MEMORIAL_DAY
2016-05-30 MEMORIAL_DAY
2017-05-29 MEMORIAL_DAY
2018-05-28 MEMORIAL_DAY
2016-01-01 NEW_YEARS_DAY
2017-01-01 NEW_YEARS_DAY
2018-01-01 NEW_YEARS_DAY
2015-12-31 NEW_YEARS_EVE
2016-12-31 NEW_YEARS_EVE
2017-12-31 NEW_YEARS_EVE
2018-12-31 NEW_YEARS_EVE