# Calculate elapsed business days using a MySQL query

I need to calculate the number of business days elapsed between date values in StartDT and EndDt.  Being able to factor out holidays would be good as well.  I've got a decent formula to do this in Excel, but need help to do this in MySQL.
``````=IF(AND(INT(StartDT)=INT(EndDt),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0)))),0,ABS(IF(INT(StartDT)=INT(EndDt),ROUND(24*(EndDt-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDt-1,HolidayList),0)+
INT(24*(((EndDt-INT(EndDt))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDt-INT(EndDt)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
``````
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Senior Principal Technical Support EngineerCommented:
>>I need to calculate the number of business days elapsed between date values in StartDT and EndDt.
I'm bit confused.. you want number of working days(Business days) between start & end date?... can you please elaborate your requirement??

I've written small function which gives me the business days between any two given  two dates(s<e) (taking Sat/Sun as off days).
Author Commented:
I do reporting on Help Desk tickets and want to show via  a MYSQL query how many working/business days (to one digit right of the decimal point) each ticket is open.  The first date is the "StartDT" value and the last date is the "EndDT" value.  Yes, Sat/Sun are off days.  Thanks!
Senior Principal Technical Support EngineerCommented:
Here is the stored procedure which returns business days/ off days list.. you may want to make use of it...

``````DELIMITER \$\$

DROP PROCEDURE IF EXISTS `uGetBussinessDays`\$\$

CREATE DEFINER=`root`@`localhost` PROCEDURE `uGetBussinessDays`(in_sDate DATE, in_eDate DATE)
BEGIN
DECLARE l_sDate DATE;
SET l_sDate = in_sDate;
DROP TABLE IF EXISTS _tblBussinessDays;
DROP TABLE IF EXISTS _tblHolidaysDays;
CREATE TEMPORARY TABLE _tblBussinessDays(BussinessDays date);
CREATE TEMPORARY TABLE _tblHolidaysDays(Offdays date);
IF ( in_sDate  > in_eDate ) THEN
SELECT "Invalid dates supplied";
END IF;
WHILE l_sDate <= in_eDate DO

IF (DAYNAME(l_sDate) = 'Sunday' ) THEN
INSERT INTO _tblHolidaysDays VALUES(l_sDate);
ELSEIF ( DAYNAME(l_sDate) = 'Saturday' ) THEN
INSERT INTO _tblHolidaysDays VALUES(l_sDate);
ELSE
INSERT INTO _tblBussinessDays VALUES(l_sDate);
END IF;

SET l_sDate = DATE_ADD(l_sDate,INTERVAL 1  DAY);

END WHILE;
SELECT BussinessDays FROM _tblBussinessDays;
SELECT Offdays FROM _tblHolidaysDays;
END\$\$

DELIMITER ;

########
Usage
########

call uGetBussinessDays('2008-12-01','2008-12-15');

=================
Bussiness Days
=================
2008-12-01
2008-12-02
2008-12-03
2008-12-04
2008-12-05
2008-12-08
2008-12-09
2008-12-10
2008-12-11
2008-12-12
2008-12-15

=================
Offdays
=================
2008-12-06
2008-12-07
2008-12-13
2008-12-14

If you need acount of bussiness days between two dates.. just modify the select and make it to return count instead of records..  same applies to offdays
``````

Experts Exchange Solution brought to you by