?
Solved

Business Hours Calculations in SQL

Posted on 2009-07-08
5
Medium Priority
?
715 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 5

Assisted Solution

by:rgc6789
rgc6789 earned 100 total points
ID: 24807941
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
ID: 24807981
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 900 total points
ID: 24808023
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
ID: 24808043
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:
Brendt Hess earned 1000 total points
ID: 24808213
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

771 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