Solved

Business Hours Calculations in SQL

Posted on 2009-07-08
5
706 Views
Last Modified: 2012-05-07
I am working on generating a report in SQL Server 2005 Reporting Services that tracks performance against various SLAs for help desk tickets in a tracking system.

Two of our SLAs involve measuring the time to respond to a ticket (difference between a time received field and time responded to field) based on whether it was received during or outside of business hours. Tickets must be responded to within 1 hour if received between 9 AM and 5 PM on a working day and by 10 AM the following working day (Monday through Friday) if received outside of that time frame or on a weekend.

Is there some way within the SQL query in the report itself or to call a stored procedure of some type that can evaluate whether or not the difference between the time received and time responded (two separate fields) to according to the SLAs outlined above? It would be good if it could provide or return a true/false value.
0
Comment
Question by:rdracer58
5 Comments
 
LVL 5

Assisted Solution

by:rgc6789
rgc6789 earned 25 total points
Comment Utility
You can create a user defined function that will determine this, however how are you going to handle holidays or do we not have to consider this?
0
 

Author Comment

by:rdracer58
Comment Utility
It would be optimal if we could consider holidays as well per defined here: http://www.opm.gov/Operating_Status_Schedules/fedhol/2009.asp; however, if this will add unnecessary complexity to the solution, just the regular business day/weekend differentiation will be fine.
0
 
LVL 16

Assisted Solution

by:Auric1983
Auric1983 earned 225 total points
Comment Utility
rdracer,

There is no standard procedure setup for that however i'm sure one could be written.

I'm not 100% happy with the function below, as it does not take into account holidays.  But this should give you a fairly good idea.
CREATE FUNCTION HelpDeskSLA 

(

	-- Add the parameters for the function here

	@StartDate Datetime,

	@EndDate DateTime

)

RETURNS varchar(10)

AS

BEGIN

	-- Declare the return variable here

	DECLARE @Result varchar(10)

	declare @DayInitiated int

	declare @DayCompleted int

	

	set @DayInitiated=datepart(weekday,@StartDate) 

	set @DayCompleted=datepart(weekday,@EndDate) 
 

set @Result='false'
 

if DateDiff(hour,@StartDate,@EndDate) < 1 

	set @Result='true'
 

-- If the start Day was a weekend did it get completed before 10am on Monday. 

if @DayInitiated = 1 or @DayInitiated=7 

begin

	if @DayCompleted=2 and (datepart(hour,@EndDate) < 10)

	set @Result = 'True' 

end
 

	RETURN @Result
 

END

Open in new window

0
 
LVL 16

Expert Comment

by:Auric1983
Comment Utility
if you have a table available that could define holidays that would be great that way you could exclude them from the measure.

0
 
LVL 32

Accepted Solution

by:
bhess1 earned 250 total points
Comment Utility
Here's a stored procedure that can determine what you are looking for, given the dates (passed as parameters).  Unfortunately, this can not be converted to a user-defined function since it uses Datepart(Weekday,....) which is nondeterministic.
CREATE PROCEDURE ResponseTimeOK (

	@Time_Received DATETIME,

	@Responded_To DATETIME

	) 
 

SET DATEFIRST 1		-- First DOW = Monday

DECLARE @Today INT

SELECT @Today = CASE  

    WHEN DATEPART(weekday, @Time_Received) BETWEEN 1 AND 4 THEN 1

	WHEN DATEPART(weekday, @Time_Received) = 5 THEN 2

	ELSE 0

END
 
 

SELECT

	CASE @Today

		WHEN 1 THEN			-- Monday - Thursday

			CASE

				WHEN Datepart(Hour, @Time_Received) >= 9 AND (Datepart(Hour, Dateadd(Minute, -1, @Time_Received)) < 17 

					THEN CASE

						WHEN DateDiff(Second, @Time_Received, @Responded_To) <= 3600	-- Responded within 1 hr

							THEN 1

						ELSE 0

					END

				WHEN DatePart(Hour, @Time_Received) < 9 

					THEN CASE

						WHEN DATEDIFF(DAY, @Time_Received, @Responded_To) = 0		-- Responded Today

							THEN CASE

								WHEN DATEPART(Hour, (DATEADD(Minute, -1, @Responded_To))) < 10	-- By 10:00AM

									THEN 1

								ELSE 0

							END

						ELSE 0													-- Responded on a later day

					END

				ELSE	-- Received after 5:00 pm 

					CASE

						WHEN DATEDIFF(DAY, @Time_Received, @Responded_To) = 1		-- Responded Next Day

							AND DATEPART(Hour, (DATEADD(Minute, -1, @Responded_To))) < 10	-- By 10:00AM

							THEN 1

						ELSE 0

					END

			END

		WHEN 2 THEN			-- Friday

			CASE

				WHEN Datepart(Hour, @Time_Received) >= 9 AND (Datepart(Hour, Dateadd(Minute, -1, @Time_Received)) < 17 

					THEN CASE

						WHEN DateDiff(Second, @Time_Received, @Responded_To) <= 3600	-- Responded within 1 hr

							THEN 1

						ELSE 0

					END

				WHEN DatePart(Hour, @Time_Received) < 9 

					THEN CASE

						WHEN DATEDIFF(DAY, @Time_Received, @Responded_To) = 0		-- Responded Today

							THEN CASE

								WHEN DATEPART(Hour, (DATEADD(Minute, -1, @Responded_To))) < 10	-- By 10:00AM

									THEN 1

								ELSE 0

							END

						ELSE 0													-- Responded on a later day

					END

				ELSE	-- Received after 5:00 pm 

					CASE

						WHEN DATEDIFF(DAY, @Time_Received, @Responded_To) = 3		-- Responded Next Monday

							AND DATEPART(Hour, (DATEADD(Minute, -1, @Responded_To))) < 10	-- By 10:00AM

							THEN 1

						ELSE 0

					END

			END

		ELSE				-- Saturday / Sunday

			CASE

				WHEN DATEDIFF(DAY, @Time_Received, @Responded_To) < 3 AND DATEPART(Weekday, @Responded_To) = 1		-- Responded Next Monday

					THEN CASE

						WHEN DATEPART(Hour, (DATEADD(Minute, -1, @Responded_To))) < 10	-- By 10:00AM

							THEN 1

						ELSE 0

					END

				ELSE 0

			END

	END AS Response_Time_OK

Open in new window

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now