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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

UmeshSenior 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).
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!
UmeshSenior 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)
    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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.