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.
