[MYSQL]networkdays and business hours

[Craig \(OFT\) Weston]

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.