CODEWITHSUNDEEP
sqloracle-database
user2736721
user2736721

Reputation: 11

Oracle SQL for Last Business Day of Current Month including federal holidays in oracle

I'm trying to find the Oracle SQL for finding the last business day of current month and also last business day of previous month. Both cases should consider the federal holidays calendar.

For Example:

I should technically get my output as 29th November since 28th is Thanksgiving holiday.

Upvotes: 1

Views: 8414

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Store those federal holidays in a holidays table as DATE type and then try something like this: Find the oldest ( MAX ) day in the last seven days of the month that is neither a Saturday or a Sunday nor a holiday mentioned in the holidays table.

The assumptions here are that 1) not all seven days at the end of the month can all be holidays or weekend and 2) Saturday and Sundays are off. You may adjust the level or where clause accordingly, depending on whether the above assumption should always hold true or not.

SELECT MAX(dt) AS last_working_day 
FROM
(
SELECT last_day(SYSDATE) - level + 1 as dt
FROM dual CONNECT BY
     level <= 7  -- the last seven days of the month
     )  WHERE TO_CHAR(dt,'DY', 'NLS_DATE_LANGUAGE = AMERICAN') NOT IN ('SAT','SUN')
     AND dt NOT IN ( SELECT holiday from federal_holidays );

A much better approach would be to have a Calendar table with all the dates of the year and predefined column called isbusinessday. Then the query would be much simpler.

SELECT MAX(dt)
  FROM calendar
  WHERE isbusinessday = 'Y' 
AND TO_CHAR(dt,'YYYYMM') = TO_CHAR(SYSDATE,'YYYYMM');

Upvotes: 4

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

Having a holiday table is working in general, however it requires some maintenance as certain holidays are moving. For example Thanksgiving Day is the 4th Thursday in November, i.e. it varies from November 22 to November 28.

You can also use the Oracle build-in Scheduler. Usually it is used to control the SCHEDULER JOBS but I don't see any reason why it should not be used for something else.

First create a list of federal holidays, for example this:

BEGIN
    DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'CHRISTMAS', repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYDATE=1225', comments => 'December 25');
    DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'COLUMBUS_DAY', repeat_interval => 'FREQ=MONTHLY;BYMONTH=OCT;BYDAY=2 MON', comments => '2nd Monday in October');
    DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'INDEPENDENCE_DAY', repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYDATE=0704', comments => 'July 4');
    DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'MARTIN_LUTHER_KING_DAY', repeat_interval => 'FREQ=MONTHLY;BYMONTH=JAN;BYDAY=3 MON', comments => '3rd Monday in January');
    DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'MEMORIAL_DAY', repeat_interval => 'FREQ=MONTHLY;BYMONTH=MAY;BYDAY=-1 MON', comments => 'Last Monday of May');
    DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'NEW_YEARS_DAY', repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYDATE=0101', comments => 'January 1');
    DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'THANKSGIVING', repeat_interval => 'FREQ=MONTHLY;BYMONTH=NOV;BYDAY=4 THU', comments => '4th Thursday in November');
    DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'WASHINGTONS_BIRTHDAY', repeat_interval => 'FREQ=MONTHLY;BYMONTH=FEB;BYDAY=3 MON', comments => '3rd Monday in February');

    DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'WEEKEND', repeat_interval => 'FREQ=DAILY;INTERVAL=1;BYDAY=SAT,SUN');

    -- Combined schedule for all federal holidays 
    DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'FEDERAL_HOLIDAYS', repeat_interval => 'FREQ=DAILY;INTERSECT=CHRISTMAS,INDEPENDENCE_DAY,MARTIN_LUTHER_KING_DAY,MEMORIAL_DAY,NEW_YEARS_DAY,THANKSGIVING,WASHINGTONS_BIRTHDAY');

END;
/

Have a look at Calendaring Syntax to see how repeat_interval needs to be specified.

Then you can use procedure DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING to get your date:

CREATE OR REPLACE FUNCTION LAST_BUSINESS_DAY(today IN TIMESTAMP DEFAULT SYSTIMESTAMP) RETURN TIMESTAMP AS 
    return_date_after TIMESTAMP := TRUNC(today);
    next_run_date TIMESTAMP;
BEGIN

    LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=DAILY;INTERVAL=1;EXCLUDE=FEDERAL_HOLIDAYS,WEEKEND', NULL, return_date_after, next_run_date);
        EXIT WHEN next_run_date >= LAST_DAY(TRUNC(today));
        return_date_after := next_run_date;
    END LOOP;

    RETURN return_date_after;   
END LAST_BUSINESS_DAY;

Upvotes: 1

Related Questions