asked on

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.

Brian Crowe

Need more sample input as opposed to more output. What would the Holidays table look like to achieve the output you show?

coreconcepts

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?

Kyle Abrahams, PMP

ASKER

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

Brian Crowe

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)

Kyle Abrahams, PMP

ASKER

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.

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kyle Abrahams, PMP

ASKER

Solved myself.