Calculate duration whilst excluding non working time

HI

I have a helpdesk application and would like to calculate the duration of a call excluding non working time.

I already have a calendar table which lists working dates/times and a function as follows:

CREATE TABLE [dbo].[iHLPWorkingHours] (

[workfromdt] [datetime] NOT NULL ,

[worktodt] [datetime] NOT NULL

Sample data:

WorkFromDt WorkToDt

02/01/2007 08:00:00 02/01/2007 18:00:00
03/01/2007 08:00:00 03/01/2007 18:00:00
04/01/2007 08:00:00 04/01/2007 18:00:00
05/01/2007 08:00:00 05/01/2007 18:00:00
06/01/2007 06/01/2007
07/01/2007 07/01/2007

Non-working days such as weekends and holidays have their times removed in the iHLPWorkingHours table.

To calculate the call duration I use the following function:

CREATE FUNCTION dbo.TotalCallDuration
(
@.fromdt DATETIME,
@.todt DATETIME
)
RETURNS INT

AS

BEGIN

RETURN
(
SELECT CAST((SUM(DATEDIFF(MINUTE,workfromdt,worktodt)) -
DATEDIFF(MINUTE,MIN(workfromdt),@.fromdt) -
DATEDIFF(MINUTE,@.todt,MAX(worktodt))) AS DECIMAL(9,2))
AS working_hours
FROM ihlpWorkingHours
WHERE NOT (@.fromdt >= worktodt OR @.todt <= workfromdt)
HAVING MIN(workfromdt) <= @.fromdt AND MAX(worktodt) >= @.todt
)
END

Then pass in the opening and closing dates from the Helpdesk Call to the function to calculate the duration in minutes:

select callid, openeddatetime,
closeddatetime, dbo.TotalCallDuration(openeddatetime, closeddatetime) as duration
from ihlpcall
statusid='closed'

This works fine when a call has been opened or closed within working hours M-F but for calls that have been opened or closed outside of these times (after 18:00 and before 08:00) M-F or Weekends the function returns a null value for the call duration.

Is there any way the function can be altered to compensate for calls opened or closed outside of working hours.

Thanks in advance.

Paul

I think this will give you the logic you need. The nested query builds a set of all to-from ranges valid for the query. It corrects the from and to dates when a full date hasn't expired. It then calculates the minutes in the ranges and sums them.

There are some more elegant solutions to this, but I think this is probably the most readible.

Please note, if a call is opened and closed outside working hours without spanning a working period, this will still return NULL.

Code Snippet

select sum(datediff(minute, x.fromdt, x.todt))
from (
select
case
when @.fromdt >= workingfromdt then @.fromdt
else workingfromdt
end as fromdt,
case
when @.todt <= workingtodt then @.todt
else workingtodt
end as todt
from ihlpworkinghours
where workingtodt >= @.fromdt AND
workingfromdt <= @.todt
) x

|||

Hi Brian,

many thanks for your response, I have tried pasting the code snippet into my function but it fails the sysntax check, with the following error:

Error 1075: RETURN statements in scalar valued functions must include an argument

am I missing something?

Thanks

Paul

|||

If you are using the code in a function, the function must return some value. Declare a variable of an appropriate type, assign the results of the SELECT statement to that variable, and then return the variable.

To keep things simple, I recommend just testing the results as a simple SELECT statement, verify it's accurate, and then work on migrating it to a function.

Good luck,
Bryan