Monday, October 24, 2011

Mysql Stored Procedure for Computing Next Business day.

In this post, I will explain how to compute next business day using stored procedure. To compute next business day, the code has to take into account Saturday, Sunday and any other bank holidays. Store the public holidays into bank_holiday table with column holiday_date of type DATETIME

Explanation: The first step is to get next day and then check if its a Saturday/Sunday, then increment accordingly (if Saturday increment by 2 days, if Sunday increment by 1 day). Next step involves checking this computed date exists in the bank_holiday table and then recursive calling itself for a match. Following is the code snippet. 
DROP PROCEDURE IF EXISTS NextBusinessDay;

CREATE PROCEDURE NextBusinessDay (IN startDate DATETIME, OUT nextBDay DATETIME)

BEGIN
 DECLARE dayOfWeek INT DEFAULT 0;
 DECLARE rowCount INT DEFAULT 0;
 
 SELECT DATE_ADD(startDate, INTERVAL 1 DAY) INTO nextBDay;
 SELECT DAYOFWEEK(nextBDay) INTO dayOfWeek;
  
 IF dayOfWeek = 7 --Saturday then working day is Monday.
  THEN SELECT DATE_ADD(nextBDay, INTERVAL 2 DAY) INTO nextBDay;
 END IF;

 IF dayOfWeek = 1 --Sunday then working day is Monday.
  THEN SELECT DATE_ADD(nextBDay, INTERVAL 1 DAY) INTO nextBDay;
 END IF;

-- Check for holiday by querying the bank_holiday table.
 SELECT count(*) from bank_holiday where holiday_date = nextBDay INTO rowCount;

 IF rowCount > 0
  THEN CALL NextBusinessDay(nextBDay,nextBDay);
 END IF;
END

For detailed explaination on built-in DATE_ADD, DAYOFWEEK mysql functions, refer to link. For a short tutorial on writing stored procedure, refer to link.


Enabling recursive stored procedure: On executing this stored procedure, you will get an error. Following is the trace:
mysql> CALL NextBusinessDay('2010-07-02 00:00:00.0', @nextBDay);
ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine NextBusinessDay

Enable recursion by passing a int value greater than 0 on mysql server startup. Following is one way:
sudo -b /usr/local/mysql/bin/mysqld_safe --max_sp_recursion_depth=3
More at link.

Executing stored procedure: Using CALL. In this case, it will be CALL NextBusinessDay('2011-10-24', @nextBDay); To retrieve the result, do select @nextBDay; (i.e. Note this is the same variable passed into Stored procedure call) Following screenshot shows tests of this procedure over some dates. (Note in US: 5th Sept is observed as Memorial day and hence its a public holiday. Also tested for Leap year 2012.)

at

2 comments:

  1. Yuriy M GoldmanFebruary 12, 2012 at 4:15 PM

    Thanks for this article. Wanted to contribute two MySQL Stored Functions that perform business day-related tasks without recursion. Hope this helps.

    -Yuriy M Goldman

    ================
    addBusinessDays
    ================
    DROP FUNCTION IF EXISTS `addBusinessDays`;
    DELIMITER ;;
    CREATE FUNCTION addBusinessDays(startDate DATETIME, numDays INT)
    RETURNS DATETIME
    DETERMINISTIC
    BEGIN
    DECLARE nextBDay DATETIME;
    DECLARE count INT;
    SET nextBDay = startDate;
    SET count = numDays;
    WHILE count > 0 DO
    IF isBankHolidayOrWeekend(nextBDay) = 0 THEN SET count = (count-1);
    END IF;
    SET nextBDay = DATE_ADD(nextBDay, INTERVAL 1 DAY);
    END WHILE;

    WHILE isBankHolidayOrWeekend(nextBDay) DO SET nextBDay = DATE_ADD(nextBDay, INTERVAL 1 DAY);
    END WHILE;
    RETURN nextBDay;
    END;;
    DELIMITER ;

    ================
    isBankHolidayOrWeekend
    ================
    DROP TABLE IF EXISTS BankHolidays;
    CREATE TABLE BankHolidays (
    holiday_date DATE,
    primary key (holiday_date)
    );


    INSERT INTO BankHolidays(holiday_date)
    VALUES
    ('2010-01-01'),
    ('2010-01-18'),
    ('2010-02-15'),
    ('2010-05-31'),
    ('2010-07-05'),
    ('2010-09-06'),
    ('2010-10-11'),
    ('2010-11-11'),
    ('2010-11-25'),
    ('2010-12-24'),
    ('2011-01-17'),
    ('2011-02-21'),
    ('2011-05-30'),
    ('2011-07-04'),
    ('2011-09-05'),
    ('2011-10-10'),
    ('2011-11-11'),
    ('2011-11-24'),
    ('2011-12-26'),
    ('2012-01-02'),
    ('2012-01-16'),
    ('2012-02-20'),
    ('2012-05-28'),
    ('2012-07-04'),
    ('2012-09-03'),
    ('2012-10-08'),
    ('2012-11-12'),
    ('2012-11-22'),
    ('2012-12-25'),
    ('2013-01-01'),
    ('2013-01-21'),
    ('2013-02-18'),
    ('2013-05-27'),
    ('2013-07-04'),
    ('2013-09-02'),
    ('2013-10-14'),
    ('2013-11-11'),
    ('2013-11-28'),
    ('2013-12-25');



    DROP FUNCTION IF EXISTS isBankHolidayOrWeekend;
    DELIMITER ;;
    CREATE FUNCTION isBankHolidayOrWeekend(input DATETIME)
    RETURNS BIT
    DETERMINISTIC
    BEGIN
    DECLARE dayOfWeek INT DEFAULT 0;
    DECLARE result BIT DEFAULT false;
    DECLARE holidayCount INT DEFAULT 0;
    SET dayOfWeek = DAYOFWEEK(input);
    SELECT COUNT(*) from BankHolidays where holiday_date = input INTO holidayCount;
    IF dayOfWeek = 7 or dayOfWeek = 1 or holidayCount > 0 THEN SET result = true;
    END IF;
    RETURN result;
    END;;
    DELIMITER ;

    ReplyDelete
  2. ChristopherSAugust 26, 2021 at 12:08 PM

    Very nice -- still works

    ReplyDelete