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
Solved

Business Hours Calculations in SQL

Posted on 2009-07-08
5
709 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
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 225 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:
bhess1 earned 250 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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert null in sql server 12 46
SubQuery link 4 35
SQL Stored Proc - Performance Enhancement 15 54
SQL trigger 5 21
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

856 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