# SQL SLA UDF - Business hours only

Hi All,

I've been doing some searching but haven't come across an elegant solution yet.  I'd like to calculate the SLA for normal business hours, with rolling dates.

Our normal business hours are 8AM -  5PM Monday-Fridays (excluding holidays)

Columns in Question for the holiday table:
EffectiveDate (date of the holiday)
isHalfday int (0/1) . . . if 1 company hours become 8:00 AM - 5:00 PM

eg:

select cast('10/14/2013' as datetime) effectiveDate
into #appCompanyHolidays

Sample output:

Start                                      End                                     Hours      Notes
10/1/2013     08:00:00          10/01/2013  12:00:00         4              Same Day
10/01/2013   08:00:00          10/01/2013  23:00:00         9              8-5 only.
10/01/2013   16:00:00          10/02/2013  10:00:00         3              4-5 Tu, 8-10 W
10/04/2013   16:00:00          10/07/2013  09:00:00         2              4-5 F, 8-9 M
10/11/2013   16:00:00          10/15/2013  11:00:00         4              4-5 F, 8-11 Tu, (holiday)
10/07/2013   07:45:00          10/09/2013  10:00:00         20            M, Tu, 8-10 W, 15 minutes excluded.

Can provide more output if needed.  Looking for something that can run relatively quickly.

LVL 42
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Need more sample input as opposed to more output.  What would the Holidays table look like to achieve the output you show?
0
Commented:
Ged, can you explain what you are attempting to return?

-- It looks like you are wanting to view a range of dates based on inputs and determine what the working schedule is and for what days,  but with the sample input inserting a row into the holidays table I don't see it - if you can clarify a little?
0
Senior .Net DeveloperAuthor Commented:
First crack below.  This is just a query for now, I'm going to use start and end dates as input params, # of hours as an output for the scalar.

Please point out if  you see any errors.

@bri:  the holidays table has an effectiveDate that has the holiday in question.  For example 10/14/2013 (columbus day) is a holiday for us.  The format is always the day at midnight (eg: 2013-10-14 00:00:00.000)

@core:
I'm trying to calculate the number of business hours between start and end date.    This will then be used to determine if we met SLA for a particular level.

``````declare @startDate datetime
declare @endDate datetime

select @startdate = '10/11/2013 8:00:00', @enddate = '10/16/2013 7:30:00'
--START DATE
set @startdate =
case
when  -- if it's a saturday
datepart(dw, @startdate) = 7
then  -- then monday 8am

when  -- if after 5:00 PM
datediff(hh, CAST(FLOOR(CAST(@startdate AS FLOAT)) AS DATETIME), @startdate) > 17
OR -- or a sunday
datepart(dw, @startdate) = 1
then -- make it 8:00 AM following day

when -- if before 8:00 AM
datediff(hh, CAST(FLOOR(CAST(@startdate AS FLOAT)) AS DATETIME), @startdate) < 8
then -- make it 8:00 AM
DATEADD(HOUR, 8, CAST(FLOOR(CAST(@startdate AS FLOAT)) AS DATETIME))

else  -- otherwise accept the date
@startdate
END

--End DATE
set @endDate =
case

when -- it's a sunday
datepart(dw, @endDate) = 1
then -- then previous Friday 5PM

when  -- if it's a saturday
datepart(dw, @endDate) = 7
OR -- if before 8:00 AM
datediff(hh, CAST(FLOOR(CAST(@endDate AS FLOAT)) AS DATETIME), @endDate) < 8
then  -- then previous day 5PM

when  -- if after 5:00 PM
datediff(hh, CAST(FLOOR(CAST(@endDate AS FLOAT)) AS DATETIME), @endDate) > 17
then -- make it 5:00 PM
DATEADD(HOUR, 17, CAST(FLOOR(CAST(@endDate AS FLOAT)) AS DATETIME))
else  -- otherwise accept the date
@endDate
END

select @startDate, @endDate,

--get the difference just in hours between the dates  eg:  4 hours, regardless of the day.
cast(datediff(ms, dateadd(d, datediff(d, @startdate, @enddate), @startdate), @enddate) as numeric(18,5))  / 3600000
+
(
--to the number of full days
datediff(d, @startdate, @endDate)
-- excluding weekends
-  ( 2 * datediff(ww, @startdate, @endDate))
--excluding holidays
-
(select count(*) from dbo.appCompanyHolidays where effectivedate between CAST(FLOOR(CAST(@startdate AS FLOAT)) AS DATETIME) and CAST(FLOOR(CAST(@endDate AS FLOAT)) AS DATETIME))
)
* 9
``````
0
Okay, I think I understand.  You want to adjust the Hours value to include any holidays that occurred between the start and end dates of the service action.

If I understand correctly then the clause below should get you the excluded holiday hours:

SELECT SUM(CASE H.isHalfday WHEN 1 THEN 4 ELSE 9 END)
FROM dbo.appCompanyHolidays AS H
WHERE H.effectivedate >= CAST(@startDate AS DATE)
AND H.effectivedate <= CAST(@endDate AS DATE)
0
Senior .Net DeveloperAuthor Commented:
Hi Bri,

I was told to forget the 1/2 days for now.  We're going to be going by the customers holiday schedule . . . which doesn't make sense to me, but hey, that's why they get paid the big bucks.

I guess my question is does the query I provide work for all cases, or can anyone point a flaw in it where this may not work?

StartTime is guaranteed to be > EndTime.
0
Senior .Net DeveloperAuthor Commented:
I ended up breaking this into multiple functions.

I needed to return both getBusinessDays and getBusinessHours as we'll be applying the SLAs differently per level.

As such, the start time / end time wrapping was the same for both, so I modularized that into their own functions.

This is not taking out holiday info for now, but can easily be included.  If different hours are needed they can be adjusted in the format functions, along with the multiplication scale (EG: * 9 would become * numHours).

``````/****** Object:  UserDefinedFunction [dbo].[fn_GetBusinessDays]    Script Date: 10/10/2013 17:27:15 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetBusinessDays]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
GO

/****** Object:  UserDefinedFunction [dbo].[fn_GetBusinessDays]    Script Date: 10/10/2013 17:27:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

@startDate datetime,
@endDate datetime)
/*
Returns Number of business Days between 2 dates.  If start > end then 0 is returned.
*/

/*
NOTE 10/14/2013 is a holiday if that's ever incorporated.

TEST CASES:

select 1 id, cast('10/07/2013 8:00:00' as datetime) start, cast('10/07/2013 11:00:00' as datetime) endd, -1 BusDays,
0 expected, cast('trivial case' as varchar(500)) TestCase
into #temp

insert into #temp
select 2, '10/07/2013 12:00:00', '10/07/2013 12:30:00', -1 ,
0, 'test minutes'

insert into #temp
select 3, '10/07/2013 16:00:00', '10/08/2013 09:00:00', -1,
1, 'wrap days, both normal'

insert into #temp
select 4, '10/07/2013 12:00:00', '10/07/2013 11:00:00', -1 ,
0, 'start after end'

insert into #temp
select 5, '10/07/2013 4:00:00', '10/07/2013 9:00:00', -1 ,
0, 'start before 8AM end normal'

insert into #temp
select 6, '10/07/2013 15:00:00', '10/07/2013 19:00:00', -1 ,
0, 'start normal end after 5'

insert into #temp
select 7, '10/07/2013 4:00:00', '10/07/2013 19:00:00', -1 ,
0, 'start before 8AM end after'

insert into #temp
select 8, '10/07/2013 17:05:00', '10/08/2013 11:00:00', -1 ,
0, 'Start after 5PM, end normal next day'

insert into #temp
select 9, '10/07/2013 17:05:00', '10/07/2013 22:00:00', -1 ,
0, 'Start after 5PM, end same day'

insert into #temp
select 10, '10/07/2013 17:05:00', '10/08/2013 6:00:00', -1 ,
0, 'Start after 5PM, end before 8AM following day'

insert into #temp
select 11, '10/18/2013 16:00:00', '10/21/2013 12:00:00', -1 ,
1, 'Start at 4PM friday, end on monday normal'

insert into #temp
select 12, '10/18/2013 17:05:00', '10/19/2013 12:00:00', -1 ,
0, 'Start at 5PM friday, end on saturday'

insert into #temp
select 13, '10/18/2013 17:05:00', '10/20/2013 12:00:00', -1 ,
0, 'Start at 5PM friday, end on sunday'

insert into #temp
select 14, '10/18/2013 16:30:00', '10/21/2013 9:00:00', -1 ,
1, 'test minutes start time friday -  monday'

insert into #temp
select 15, '10/18/2013 16:00:00', '10/21/2013 8:30:00', -1 ,
1, 'test minutes end time friday -  monday'

insert into #temp
select 16, '10/19/2013 16:00:00', '10/20/2013 8:30:00', -1 ,
0, 'start satuday end sunday'

insert into #temp
select 17, '10/19/2013 16:00:00', '10/21/2013 8:30:00', -1 ,
0, 'start saturday end monday normal'

insert into #temp
select 18, '10/20/2013 16:00:00', '10/21/2013 8:30:00', -1 ,
0, 'start sunday end monday normal'

update #temp

select * from #temp
order by id

select * from #temp
where BusDays != expected
order by id

drop table #temp

*/

returns int
as
begin

declare @days int

--START DATE
set @startdate = dbo.SLA_FormatStartDate(@startdate)

--End DATE
set @endDate = dbo.SLA_FormatEndDate (@endDate)

if @startdate >= @enddate
set @days =  0
else
set
@days =
(
--to the number of full days
datediff(d, @startdate, @endDate)
-- excluding weekends
-  ( 2 * datediff(ww, @startdate, @endDate))
--don't exclude holidays for now
/*
--excluding holidays
---(select count(*) from dbo.appCompanyHolidays where effectivedate between CAST(FLOOR(CAST(@startdate AS FLOAT)) AS DATETIME) and CAST(FLOOR(CAST(@endDate AS FLOAT)) AS DATETIME))
*/
)

return case when @days < 0 then 0 else @days end

end

GO

/****** Object:  UserDefinedFunction [dbo].[fn_GetBusinessHours]    Script Date: 10/10/2013 17:27:28 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetBusinessHours]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
GO

/****** Object:  UserDefinedFunction [dbo].[fn_GetBusinessHours]    Script Date: 10/10/2013 17:27:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

@startDate datetime,
@endDate datetime)
/*
Returns Number of business hours between 2 dates.  If start > end then 0 is returned.

*/

/*
NOTE 10/14/2013 is a holiday if that's ever incorporated.

TEST CASES:

select 1 id, cast('10/07/2013 8:00:00' as datetime) start, cast('10/07/2013 11:00:00' as datetime) endd, cast(-1 as numeric(18,2))  hrs,
cast(3 as numeric (18,2)) expected, cast('trivial case' as varchar(500)) TestCase
into #temp

insert into #temp
select 2, '10/07/2013 12:00:00', '10/07/2013 12:30:00', cast(-1 as numeric(18,2)),
0.5, 'test minutes'

insert into #temp
select 3, '10/07/2013 16:00:00', '10/08/2013 09:00:00', -1,
2, 'wrap days, both normal'

insert into #temp
select 4, '10/07/2013 12:00:00', '10/07/2013 11:00:00', cast(-1 as numeric(18,2)),
0, 'start after end'

insert into #temp
select 5, '10/07/2013 4:00:00', '10/07/2013 9:00:00', cast(-1 as numeric(18,2)),
1, 'start before 8AM end normal'

insert into #temp
select 6, '10/07/2013 15:00:00', '10/07/2013 19:00:00', cast(-1 as numeric(18,2)),
2, 'start normal end after 5'

insert into #temp
select 7, '10/07/2013 4:00:00', '10/07/2013 19:00:00', cast(-1 as numeric(18,2)),
9, 'start before 8AM end after'

insert into #temp
select 8, '10/07/2013 17:05:00', '10/08/2013 11:00:00', cast(-1 as numeric(18,2)),
3, 'Start after 5PM, end normal next day'

insert into #temp
select 9, '10/07/2013 17:05:00', '10/07/2013 22:00:00', cast(-1 as numeric(18,2)),
0, 'Start after 5PM, end same day'

insert into #temp
select 10, '10/07/2013 17:05:00', '10/08/2013 6:00:00', cast(-1 as numeric(18,2)),
0, 'Start after 5PM, end before 8AM following day'

insert into #temp
select 11, '10/18/2013 16:00:00', '10/21/2013 12:00:00', cast(-1 as numeric(18,2)),
5, 'Start at 4PM friday, end on monday normal'

insert into #temp
select 12, '10/18/2013 17:05:00', '10/19/2013 12:00:00', cast(-1 as numeric(18,2)),
0, 'Start at 5PM friday, end on saturday'

insert into #temp
select 13, '10/18/2013 17:05:00', '10/20/2013 12:00:00', cast(-1 as numeric(18,2)),
0, 'Start at 5PM friday, end on sunday'

insert into #temp
select 14, '10/18/2013 16:30:00', '10/21/2013 9:00:00', cast(-1 as numeric(18,2)),
1.5, 'test minutes start time friday -  monday'

insert into #temp
select 15, '10/18/2013 16:00:00', '10/21/2013 8:30:00', cast(-1 as numeric(18,2)),
1.5, 'test minutes end time friday -  monday'

insert into #temp
select 16, '10/19/2013 16:00:00', '10/20/2013 8:30:00', cast(-1 as numeric(18,2)),
0, 'start satuday end sunday'

insert into #temp
select 17, '10/19/2013 16:00:00', '10/21/2013 8:30:00', cast(-1 as numeric(18,2)),
0.5, 'start saturday end monday normal'

insert into #temp
select 18, '10/20/2013 16:00:00', '10/21/2013 8:30:00', cast(-1 as numeric(18,2)),
0.5, 'start sunday end monday normal'

update #temp

select * from #temp
order by id

select * from #temp
where hrs != expected
order by id

drop table #temp

*/

returns numeric(18,2)
as
begin

declare @hours numeric(18,2)

--START DATE
set @startdate = dbo.SLA_FormatStartDate(@startdate)

--End DATE
set @endDate = dbo.SLA_FormatEndDate (@endDate)

if @startdate >= @enddate
set @hours =  0
else
set
@hours =
--get the difference just in hours between the dates  eg:  4 hours 30 minutes, regardless of the day.
cast(datediff(ms, dateadd(d, datediff(d, @startdate, @enddate), @startdate), @enddate) as numeric(18,5))  / 3600000
+
(
--to the number of full days
datediff(d, @startdate, @endDate)
-- excluding weekends
-  ( 2 * datediff(ww, @startdate, @endDate))
--don't exclude holidays for now
/*
--excluding holidays
---(select count(*) from dbo.appCompanyHolidays where effectivedate between CAST(FLOOR(CAST(@startdate AS FLOAT)) AS DATETIME) and CAST(FLOOR(CAST(@endDate AS FLOAT)) AS DATETIME))
*/
)
* 9

return case when @hours < 0 then 0 else @hours end

end

GO

/****** Object:  UserDefinedFunction [dbo].[SLA_FormatEndDate]    Script Date: 10/10/2013 17:27:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SLA_FormatEndDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SLA_FormatEndDate]
GO

/****** Object:  UserDefinedFunction [dbo].[SLA_FormatEndDate]    Script Date: 10/10/2013 17:27:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create function [dbo].[SLA_FormatEndDate](@endDate dateTime)
returns datetime
as
begin

declare @newDate datetime

set @newDate = 		case

when -- it's a sunday
datepart(dw, @endDate) = 1
then -- then previous Friday 5PM

when  -- if it's a saturday
datepart(dw, @endDate) = 7
OR -- if before 8:00 AM
datepart(hh, @endDate) < 8
then  -- then previous day 5PM

when  -- if after 5:00 PM
datepart(hh, @enddate) >= 17
then -- make it 5:00 PM
DATEADD(HOUR, 17, CAST(FLOOR(CAST(@endDate AS FLOAT)) AS DATETIME))
else  -- otherwise accept the date
@endDate
END
return @newDate

end

GO

/****** Object:  UserDefinedFunction [dbo].[SLA_FormatStartDate]    Script Date: 10/10/2013 17:27:55 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SLA_FormatStartDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SLA_FormatStartDate]
GO

/****** Object:  UserDefinedFunction [dbo].[SLA_FormatStartDate]    Script Date: 10/10/2013 17:27:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create function [dbo].[SLA_FormatStartDate](@startdate datetime)
returns datetime
as
begin
declare @newDate datetime

set @newDate =
case
when  -- if it's a saturday
datepart(dw, @startdate) = 7
then  -- then monday 8am

when  --friday after 5 pm
datepart(dw, @startdate) = 6 and datepart(hh, @startdate) >= 17
then  -- then monday 8am

when  -- if after 5:00 PM on another day
datepart(hh, @startdate) >= 17
OR -- or a sunday
datepart(dw, @startdate) = 1
then -- make it 8:00 AM following day

when -- if before 8:00 AM
datepart(hh, @startdate) < 8
then -- make it 8:00 AM
DATEADD(HOUR, 8, CAST(FLOOR(CAST(@startdate AS FLOAT)) AS DATETIME))

else  -- otherwise accept the date
@startdate
end

return @newDate

end

GO
``````
0

Experts Exchange Solution brought to you by