A function to calculate working days and substracting holidays within a period in MySQL

I needed to calculate the working days between the date a request was made and today, searching the web I found this was a common request and found several ideas or code but most of them lack the ability to include the holidays.

As we already have something like this working on PHP I grab the idea of using a table containing the holidays as dates. And the code I found this thread appeal me for its simplicity
http://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates
And code from
http://stackoverflow.com/users/554795/muhammad-haseeb-khan

However this FUNCTION didn't consider the holidays and for my specific needs it added one more day as my period of time starts the next day after the request is made, so I modify it to cover my needs.

I would like to note that on my first attempt to run this function, MySQL thrown out an error
"#1436 - Thread stack overrun:  6136 bytes used of a 131072 byte stack, and 128000 bytes needed."
So I modify My.CNF and increased the Thread Stack to 256K

Also this function includes a QUERY so it may not work on all versions of MySQL, it was tested and implemented on version 5.1.54-1 running on an UBUNTU box.

This is the CREATE for the holidays table

DROP TABLE IF EXISTS `YourDataBase`.`holidays`;
                      CREATE TABLE  `YourDataBase`.`holidays` (
                        `DATE` DATE DEFAULT NOT NULL,
                        `ID` int(10) NOT NULL AUTO_INCREMENT,
                        PRIMARY KEY (`ID`)
                      ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

Select all Open in new window

Then you load your holidays; 2013-01-01,2013-MM-DD...
And you end with a table like this, part of the Mexican government holidays for 2013
'2013-01-01'      1
'2013-01-02'      2
'2013-01-03'      3
'2013-01-04'      4

Then you can make a SELECT COUNT for a period of time and you will get the number of holidays that happened on that period.

Also by using this kind of table it is very easy to keep it updated as in most countries there are few holidays per year

As for getting the working days, Monday through Friday, here is the CREATE for the function I found on another site with minor modifications to subtract the holidays that occurred on the period.

DELIMITER $$
                      
                      DROP FUNCTION IF EXISTS `YourDataBase`.`WORKDAYS`$$
                      CREATE FUNCTION  `YourDataBase`.`WORKDAYS`(first_date DATETIME, second_date DATETIME) RETURNS int(11)
                          
                      DETERMINISTIC
                      
                      BEGIN
                      
                      DECLARE start_date DATE;
                      DECLARE end_date DATE;
                      DECLARE diff INT;
                      DECLARE NumberOfWeeks INT;
                      DECLARE RemainingDays INT;
                      DECLARE firstDayOfTheWeek INT;
                      DECLARE lastDayOfTheWeek INT;
                      DECLARE WorkingDays INT;
                      DECLARE holiday INT;
                      
                      IF (first_date < second_date) THEN
                      SET start_date = first_date;
                      SET end_date = second_date;
                      ELSE
                      SET start_date = second_date;
                      SET end_date = first_date;
                      END IF;
                      ## In the original code one was added at the end of the statement to include both days in interval
                      SET diff = DATEDIFF(end_date, start_date);
                      SET NumberOfWeeks=FLOOR(diff/7);
                      SET RemainingDays=MOD(diff,7);
                      SET firstDayOfTheWeek=DAYOFWEEK(start_date);
                      SET lastDayOfTheWeek=DAYOFWEEK(end_date);
                      IF(firstDayOfTheWeek <= lastDayOfTheWeek) THEN
                         IF( firstDayOfTheWeek<=6 AND 6 <=lastDayOfTheWeek) THEN SET        RemainingDays=RemainingDays-1; END IF;
                         IF( firstDayOfTheWeek<=7 AND 7 <=lastDayOfTheWeek) THEN SET RemainingDays=RemainingDays-1; END IF;
                         ELSE
                             IF( firstDayOfTheWeek=7) THEN SET RemainingDays=RemainingDays-1;
                               IF (lastDayOfTheWeek=6) THEN  SET RemainingDays=RemainingDays-1; END IF;
                             ELSE SET RemainingDays=RemainingDays-2;
                             END IF;
                         END IF;
                      #Here we count the holidays that had occured during the period where are testing
                      SELECT COUNT(DATE) INTO @DESC
                      FROM holidays
                      WHERE FECHA BETWEEN start_date AND end_date;
                      SET holiday=@DESC;
                         SET WorkingDays=NumberOfWeeks*5;
                      #Here we substract the number of holidays from the working week
                         IF(RemainingDays>0) THEN RETURN WorkingDays+RemainingDays-holiday;
                         ELSE RETURN WorkingDays-holiday; END IF;
                       END;
                       $$
                      DELIMITER ;

Select all Open in new window

By using the same initial and final dates on the calculation of working days and holidays that occurred during that period you obtain the real working days on a period as you substract the holidays that occurred on that period.
You just need to include this statement on your SELECT query
WORKDAYS(FirstDate,FinalDate)
Normally I use CURRENT_DATE as the final date, but you can use any date.