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))))))

Open in new window

hdgarminAsked:
Who is Participating?
 
UmeshMySQL Principle 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)
    READS SQL DATA
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

Open in new window

0
 
UmeshMySQL Principle 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).
0
 
hdgarminAuthor 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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.