Solved

Calculate elapsed business days using a MySQL query

Posted on 2008-11-12
Medium Priority
2,062 Views
Last Modified: 2012-05-05
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))))))
``````
0
Question by:hdgarmin
• 2
3 Comments

LVL 26

Expert Comment

ID: 23085296
>>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

Author Comment

ID: 23089368
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

LVL 26

Accepted Solution

Umesh earned 600 total points
ID: 23093072
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
``````
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month14 days, 6 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.