Peter, Baron and all,

I think that I am almost there. Here's my query to return
cycle time in hours:minutes:seconds accounting for business hours. There
is some discussion as to when a ticket cycle time ends - for example if
a person works on a ticket at 10:00 at night and closes it, it should
end then, not at close of business that day.



Thank you for your . This is more complex than I really
feel qualified for and you have really ed me.



Regards,

Craig





DELIMITER $$



DROP FUNCTION IF EXISTS `BizHoursTimeDiff` $$



CREATE DEFINER=`root`@`localhost` FUNCTION `BizHoursTimeDiff`( d1
DATETIME, d2 DATETIME ) RETURNS char(30) CHARSET latin1

DETERMINISTIC

BEGIN

DECLARE dow1, dow2, days, wknddays INT;

DECLARE tdiff CHAR(20);

SET dow1 = DAYOFWEEK(d1);

SET dow2 = DAYOFWEEK(d2);

set @dayEnd = (select time(`business_hours`.`Day_End`) from
`business_hours` limit 1);

SET @dayStart = (select time(`business_hours`.`Day_Start`) from
`business_hours` limit 1);



set @d1 = if (TIME(d1) > @dayEnd,@dayEnd,d1);





set @d1 = if (TIME(d1) <

@dayStart,@dayStart,d1);





#set @d2 = if (TIME(@d2) < @dayStart,@dayStart,@d2);



#set @d2 = if @dayEnd,@dayEnd,@d2);



SET tdiff = TIMEDIFF( TIME(d2), TIME(d1) );

SET days = DATEDIFF(d2,d1);

SET 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 )

)

)

);

SET @tdiff = tdiff;

SET days = FLOOR(days - wkndDays) - (IF( ASCII(tdiff) = 45, 1, 0 ) +
(SELECT count(*) FROM `holidays` WHERE `holidays`.`date` BETWEEN d1 AND
d2 AND WEEKDAY(`Holidays`.`date`)<5));

SET tdiff = IF( ASCII(tdiff) = 45, TIMEDIFF( '24:00:00',
SUBSTRING(tdiff,2)), TIMEDIFF( tdiff, '00:00:00' ));



SET @hr = days * left((@dayEnd - @dayStart),2) + left(tdiff,2);

SET @min = mid(tdiff,4,2);

SET @sec = mid(tdiff,7,2);



RETURN concat_ws(':',@hr,@min,@sec);



END $$



DELIMITER ;
--------------------------------------------------------
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.