[MYSQL]networkdays and business hours

From: Weston, Craig (OFT) <Craig.Weston(at)oft.state.ny.us>
Date: Tue Jul 31 2007 - 08:41:47 EDT

Hi there,
I am trying to compute "cycle time" between two dates in mysql. This is a measurement of business hours between two datetimes, in hours. In Excel, I can use the "networkdays" function to get partway there. What I am trying to calculate is:

# hours between start time and resolved time, excluding weekend days, non business hours (business hours are 0700 to 16:00) and holidays. I have a list of holidays

Based on some of the queries I have looked at in http://www.artfulsoftware.com/infotree/mysqlquerytree.php I have so far an equation that will take into account the weekends, I think:

@dow1 := DAYOFWEEK('d1') AS dow1,
@dow2 := DAYOFWEEK('d2') AS dow2,
@days := DATEDIFF('d2','d1') AS Days,
@wknddays := 2 * FLOOR( @days / 7 ),

if(@dow1 = 1 AND @dow2 > 1, 1,if(@dow1 = 7 AND @dow2 = 1, 1,if(@dow1 > 1 AND @dow1 > @dow2, 2,if(@dow1 < 7 AND @dow2 = 7, 1, 0)))) AS WkndDays,
@days - @wkndDays AS BizDays

At least this is close to the original as posted over at artful. I need the number in hours instead of days however so I am exploring with TIMEDIFF instead of datediff.

For further background, here is the equation in Excel:

=IF(C2="","",(IF(H2=1,((EndDT)-(D2)),(NETWORKDAYS(D2,EndDT,HolidayList)- 1)*(DayEnd-DayStart)+IF(NETWORKDAYS(D2,EndDT,HolidayList),MEDIAN(MOD(End DT,1),DayEnd,DayStart),DayEnd)-MEDIAN(NETWORKDAYS(EndDT,D2,HolidayList)* MOD(D2,1),DayEnd,DayStart))*24))

Note that the Excel equation takes into account conditions I haven't mentioned above,specifically if data doesn't exist, and if the site name is listed on a "critical site" list and thus gets 24 hour support.

Does anyone have any ideas to further this quest?

---

This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. Received on Tue Jul 31 08:43:09 2007