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 help. This is more complex than I really
feel qualified for and you have really helped me.
Regards,
Craig
DELIMITER $$
DROP FUNCTION IF EXISTS `BizHoursTimeDiff` $$
CREATE [EMAIL PROTECTED] 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.
