Problem Statement:
From the given date, write a function/procedure to calculate the next business day. The new returned date should not be in (SAT, SUN or Holidays).
e.g.: if the given date is 07-oct-2011, the next business day should be 11-oct-2011. Since 10-oct-2011 is a holiday.
Design:
– Create a holidays list table for a given year.
– keep the holidays dates in a list
– check the day of the given date, if it is friday then increment the day by 3 days. or else if it is Saturday then increment by 2 days or else increment by 1 day.
– check whether incremented date falls on any of the holidays, if it does, again increment the day based on the day of the week.
Here is the oracle procedure code.
/
CREATE OR REPLACE PROCEDURE test.sp_business_day
AS
CURSOR c1
IS
SELECT holiday_dt
FROM t_holiday_list;
next_business_date DATE;
processing_date DATE;
day_of_the_week number;
BEGIN
SELECT proc_dt
INTO processing_date
FROM test.t_proc_dt;
SELECT TO_CHAR(PROC_DT, 'D') INTO DAY_OF_THE_WEEK FROM T_PROC_DT;
--DBMS_OUTPUT.put_line ('day of the week from proc_dt is:'|| DAY_OF_THE_WEEK);
if DAY_OF_THE_WEEK = '6' then -- if day is Friday, then next business is Monday.
NEXT_BUSINESS_DATE := processing_date + 3;
--DBMS_OUTPUT.put_line ('added 3 days to date and new date is:' || processing_date);
elsif DAY_OF_THE_WEEK = '7' then
NEXT_BUSINESS_DATE := processing_date + 2;
else
NEXT_BUSINESS_DATE := processing_date + 1;
end if;
DBMS_OUTPUT.put_line ('next proc_dt is:' || NEXT_BUSINESS_DATE);
FOR c1rec IN c1
LOOP
BEGIN
IF (TRUNC (NEXT_BUSINESS_DATE) = TRUNC (c1rec.holiday_dt))
THEN
DBMS_OUTPUT.put_line ('HOLIDAY DAY FOUND:');
SELECT TO_CHAR(NEXT_BUSINESS_DATE, 'D') INTO DAY_OF_THE_WEEK FROM T_PROC_DT;
if DAY_OF_THE_WEEK = '6' then
NEXT_BUSINESS_DATE := NEXT_BUSINESS_DATE + 3;
elsif DAY_OF_THE_WEEK = '2' then
NEXT_BUSINESS_DATE := NEXT_BUSINESS_DATE + 1;
elsif DAY_OF_THE_WEEK = '5' then
NEXT_BUSINESS_DATE := NEXT_BUSINESS_DATE + 1;
end if;
END IF;
END;
END LOOP;
DBMS_OUTPUT.put_line ('Next Business Day is:' || NEXT_BUSINESS_DATE);
END sp_business_day;
/
