[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Business Hours Calculations in SQL

Posted on 2009-07-08
5
Medium Priority
?
717 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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