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.

Thanks in advanced.
LVL 42
Kyle AbrahamsSenior .Net DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Brian CroweDatabase AdministratorCommented:
Need more sample input as opposed to more output.  What would the Holidays table look like to achieve the output you show?
0
coreconceptsCommented:
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
Kyle AbrahamsSenior .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'
--normalize business hours
--START DATE
set @startdate = 
					case 
						when  -- if it's a saturday 
							datepart(dw, @startdate) = 7 
						then  -- then monday 8am
							dateadd(d, 2, DATEADD(HOUR, 8, CAST(FLOOR(CAST(@startdate AS FLOAT)) AS DATETIME)))
					    
					    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
					 		dateadd(d, 1, DATEADD(HOUR, 8, CAST(FLOOR(CAST(@startdate AS FLOAT)) AS DATETIME)))
				
						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
							dateadd(d, -2, DATEADD(HOUR, 17, CAST(FLOOR(CAST(@endDate AS FLOAT)) AS DATETIME)))
							
						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
							dateadd(d, -1, DATEADD(HOUR, 17, CAST(FLOOR(CAST(@endDate AS FLOAT)) AS DATETIME)))
					    
					    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
-- add that 
+ 
(
	--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 

Open in new window

0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Brian CroweDatabase AdministratorCommented:
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
Kyle AbrahamsSenior .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
Kyle AbrahamsSenior .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'))
DROP FUNCTION [dbo].[fn_GetBusinessDays]
GO

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

SET QUOTED_IDENTIFIER ON
GO



CREATE function [dbo].[fn_GetBusinessDays](
@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
 set BusDays = dbo.fn_GetBusinessDays(start, endd)
 
 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




--normalize business hours
--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'))
DROP FUNCTION [dbo].[fn_GetBusinessHours]
GO

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

SET QUOTED_IDENTIFIER ON
GO


CREATE function [dbo].[fn_GetBusinessHours](
@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
 set hrs = dbo.fn_GetBusinessHours(start, endd)
 
 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)




--normalize business hours
--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
-- add that 
+ 
(
	--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
							dateadd(d, -2, DATEADD(HOUR, 17, CAST(FLOOR(CAST(@endDate AS FLOAT)) AS DATETIME)))
							
						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
							dateadd(d, -1, DATEADD(HOUR, 17, CAST(FLOOR(CAST(@endDate AS FLOAT)) AS DATETIME)))
					    
					    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
							dateadd(d, 2, DATEADD(HOUR, 8, CAST(FLOOR(CAST(@startdate AS FLOAT)) AS DATETIME)))
					    
					    when  --friday after 5 pm
					        datepart(dw, @startdate) = 6 and datepart(hh, @startdate) >= 17
					    then  -- then monday 8am
							dateadd(d, 3, DATEADD(HOUR, 8, CAST(FLOOR(CAST(@startdate AS FLOAT)) AS DATETIME)))
							
					    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
					 		dateadd(d, 1, DATEADD(HOUR, 8, CAST(FLOOR(CAST(@startdate AS FLOAT)) AS DATETIME)))
				
						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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
Solved myself.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.