Recursively use a function to exclude Weekends and National Holidays to find a due date

[Pandit P]

Hello everyone,

I a written a UDF and a stored procedure to calculate the due date after excluding the Weekends and the National (public holidays from a mysql table.

But it is not working as expected. I appreciate if anyone can guide me on this.

Below is a table structure that I have for storing the Holidays.

  CREATE TABLE `holiday_list` (
  `id` char(36) NOT NULL AUTO_INCREMENT,
  `holiday_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 -- insert values

 INSERT INTO `holiday_list`(holiday_date) VALUES
 ('2019-03-12'),
 ('2019-03-13'),
 ('2019-03-14'),
 ('2019-03-15'),
 ('2019-03-29');

 
-- Below is a function which is used to filter the weekends and also a stored prcoedure is called within (recursive) the same, which in turn calls the same function to check for the due date after recursively filtering the Weekends and the public holidays to find the working due date. We are passing the flag as parameter to the function to either forward (when the Hoidays count is greater than 0) or exit (when the number of Holidays is 0).

CREATE FUNCTION Fn_CalWorkingDay (iDate varchar(12), iWD smallint(3), flag char(1)) RETURNS varchar(12) CHARSET utf8
NOT DETERMINISTIC
CONTAINS SQL
BEGIN
DECLARE DueDate varchar(12);
DECLARE error BOOLEAN DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = TRUE;

IF(iDate is not null and iDate <> '' and iWD > 0) THEN  

    IF(flag = 'F') THEN
            SET iDate = str_to_date(iDate, '%Y-%m-%d');
            SET @ID:=iDate; -- initial_date,  the initial date in the right format to manipulate (add x working day)
            SET @NWD:=iWD; -- days_to_add, number of working days to add (excluding Saturday & Sunday)
            SET @DA:= DATE_ADD(iDate, INTERVAL iWD DAY); -- as date_add
            SET @LASTDAY := WEEKDAY(@DA); -- as last_day, -- the day (Monday, Tuesday...) corresponding to the initial date + number of days to add
            SET @WEEK1 := DATE_FORMAT(@ID, '%v'); -- as initial_date_week, format the initial date to match week mode 3 (Monday 1-53)
            SET @WEEK2 := DATE_FORMAT(@DA, '%v'); -- as added_date_week_nbr, the week # of the initial_date + number of days to add
            SET @WEEKDIFF := @WEEK2 - @WEEK1; -- as week_difference, -- the difference between week 2 and week 1
            SET DueDate = DATE_ADD(@ID,
                INTERVAL @NWD + 
                    if ( @WEEKDIFF > 0 or @LASTDAY in (5,6),
                      2,
                      0
                      ) + 
                     if (@WEEKDIFF > 1,
                     @WEEKDIFF*2,
                     0
                     ) DAY
            ); 
       
       CALL Sp_Check_For_Holidays(iDate, DueDate, @HolidayCount);
       IF(@HolidayCount > 0) THEN
            SET DueDate = DATE_ADD(DueDate, INTERVAL @HolidayCount DAY);
       END IF;

       RETURN DueDate; -- @NewDate;
    ELSE
       RETURN iDate;
    END IF;
  
ELSE
    RETURN iDate;
END IF;

END;


=======================================================

-- Stored procedure "Sp_Check_For_Holidays" which is called in the above function.

CREATE PROCEDURE Sp_Check_For_Holidays (
IN iStartDate varchar(12), 
IN iEndDate varchar(12),
OUT NewDate varchar(12),
OUT HolidaysCount smallint(3)
)
NOT DETERMINISTIC
CONTAINS SQL
proc:
BEGIN
DECLARE error BOOLEAN DEFAULT FALSE;

SET max_sp_recursion_depth=15;

SELECT count(*) INTO HolidaysCount from holiday_list;

IF(HolidaysCount > 0) THEN
    SET NewDate =  (SELECT Fn_Cal_working_day(iEndDate, HolidaysCount, 'F'));
ELSE
    SET NewDate = (SELECT Fn_Cal_working_day(iEndDate, HolidaysCount, 'E'));
END IF;

END;

When we use the above function "Fn_CalWorkingDay" in a query,

SELECT Fn_CalWorkingDay('2019-03-05', 5, 'F');

it should give the due date as 2019-03-18, since we have added the Holidays in the table from 2019-03-12 to 2019-03-15. Since the function "Fn_CalWorkingDay" excludes the weekends (SAT & SUNDAY) again after checking for the Holidays recurisvely from 2019-03-12 to 2019-03-15.


Please help.

Thanks in advance.

P Pandit