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

[Alexandre Silva]

Try this way:

-- Below is a Stored procedure which is used to filter the weekends and also another stored prcoedure is called within (recursive) the same, which in turn calls the same SP 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).

DELIMITER //
CREATE PROCEDURE Sp_CallWorkingDay (
IN iDate VARCHAR(12), IN iWD smallint(3), IN flag char(1), OUT DueDateFinal VARCHAR(12)
)
NOT DETERMINISTIC
CONTAINS SQL
proc:
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, @NewDate, @HolidayCount);
       
       SET DueDateFinal = @NewDate;
    ELSE
       SET DueDateFinal = iDate;
    END IF;
  
ELSE
    SET DueDateFinal = iDate;
END IF;

END;//
DELIMITER ;



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

DELIMITER //
CREATE PROCEDURE Sp_Check_For_Holidays (
IN iStartDate varchar(12), 
IN iEndDate varchar(12),
OUT NewDate varchar(12),
OUT HolidaysCount INT(11)
)
NOT DETERMINISTIC
CONTAINS SQL
proc:
BEGIN
DECLARE error BIT(1) DEFAULT FALSE;

SET max_sp_recursion_depth=15;

SELECT count(*) INTO HolidaysCount from holiday_list WHERE holiday_date > iStartDate AND holiday_date <= iEndDate;

IF(HolidaysCount > 0) THEN
    CALL Sp_CallWorkingDay(iEndDate, HolidaysCount, 'F', NewDate);
ELSE
    CALL Sp_CallWorkingDay(iEndDate, HolidaysCount, 'E', NewDate);
END IF;

END;//
DELIMITER ;

And finally:

CALL Sp_CallWorkingDay('2019-03-05', 5, 'F', @WantedDate); SELECT @WantedDate;

returns 2019-03-18