Business Hours Calculations in SQL

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.
rdracer58Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rgc6789Commented:
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
rdracer58Author Commented:
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
Auric1983Commented:
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
Auric1983Commented:
if you have a table available that could define holidays that would be great that way you could exclude them from the measure.

0
Brendt HessSenior DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.