Hello - I have the below function (source: http://ask.sqlteam.com/questions/1105/regarding-sql-query-further-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!