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:
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.
|• [MYSQL]networkdays and business hours||Craig \(OFT\) Weston||31 Jul|