SQL Business Hours Calculation

[QuestionBoy]

Hello - I have the below function (source: http://ask.sqlteam.com/questions/110...urther-queries) that basically calculates the business hours/minutes elapsed between two **smalldatetime** fields:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
ALTER FUNCTION [dbo].[getBusinessHours] (@smalldatetime1 smalldatetime, @smalldatetime2 smalldatetime)
 
RETURNS bigint 
 
AS
 
BEGIN
 
DECLARE @Diff bigint; 
DECLARE @adjusted_1 smalldatetime; 
DECLARE @adjusted_2 smalldatetime;
 
SET @adjusted_1 = case when @smalldatetime1 - dateadd(day, datediff(day, 0, @smalldatetime1), 0) < '18:00' then @smalldatetime1 else dateadd(day, datediff(day, 0, @smalldatetime1), 0) + case when datename(weekday,@smalldatetime1) in ('saturday','sunday') then '17:30' else '18:00' end end;
 
SET @adjusted_2 = case when @smalldatetime2 - dateadd(day, datediff(day, 0, @smalldatetime2), 0) > '08:30' then @smalldatetime2 else dateadd(day, datediff(day, 0, @smalldatetime2), 0) + case when datename(weekday,@smalldatetime1) in ('saturday','sunday') then '09:30' else '08:30' end end;
 
SET @Diff = case when datename(weekday,@smalldatetime1) in ('saturday','sunday') then datediff(minute, @adjusted_1, @adjusted_2) - (datediff(day, @adjusted_1, @adjusted_2) * 960) else datediff(minute, @adjusted_1, @adjusted_2) - (datediff(day, @adjusted_1, @adjusted_2) * 870) end;
 
return @Diff
 
END

The problem I am facing is that the function fails to calculate the business hours correctly if the "**smalldatetime1**" falls between **12:00 AM** and **8:30 AM**.

Also FYI -

- smalldatetime1 is the date/time when a call is logged into the database.
- smalldatetime2 is the date/time when the call was closed.

Now basically I would like to track the calls that took more than 24 hrs / 4 hrs to close, only considering business hours, which is:

- 8:30 AM - 6:00 PM on Weekdays
- 9:30 AM - 5:30 PM on Weekends

Any help would be much appreciated.

Thanks!

[Delerna]

How about this

In your function the first thing you should do is check @smalldatetime1 to see if it is less than the start time for that day.
If it is then adjust @smalldatetime1 so that it is equal to the start time for that day.

Now do your calciulation using the adjusted
@smalldatetime1

[QuestionBoy]

@Delerna
Hello - thanks for your response.

Much appreciate it.