?
Solved

Calculate elapsed business days using a MySQL query

Posted on 2008-11-12
4
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))))))

Open in new window

0
Comment
Question by:hdgarmin
  • 2
3 Comments
 
LVL 26

Expert Comment

by:Umesh
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

by:hdgarmin
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

by:
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

Open in new window

0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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

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.

Join & Ask a Question