A nice feature about working with Service Manager is how easy it is to get data out of the system. Many reports are available out-of-box, either with Cireson Analytics, SCSM DataWarehouse, or basic SCSM views. But sometimes, the data that you are looking for is there, but needs to be massaged before it’s ready for use.
A recent Key Performance Indicator that a client of mine wanted was Average Time To Resolve (ATTR). This would normally be very easy to grab from the ServiceManagement database, WorkItem table. Something like:
select WorkItem.WorkItemId ,WorkItem.Created ,WorkItem.ResolvedDate ,DATEDIFF(HOUR, WorkItem.Created, WorkItem.ResolvedDate) as ATTR from ServiceManagement.dbo.WorkItem where WorkItem.ResolvedDate is not null
This query isn’t bad, and works in general for a number of environments, especially when dealing with averages across a large number of tickets. But the main problem is that it accounts for total time, not actual time during a normal work day. For example, if an incident is opened on Tuesday at 10pm and resolved on Wednesday at 10am, then the ATTR shows as 12 hours. But if the work day starts at 8am, then the real ATTR should be 2 hours.
It becomes apparent that the DATEDIFF function alone cannot solve our issue. We need something more complex. As any SQL admin knows, sometimes your answer is already available on Google. Doing a quick search, we end up at https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/ , which shows a fantastic function called fn_GetTotalWorkingHours, made by Sifiso W. Ndlovu. Skipping the very in-depth explanation of this function, we end up with this:
CREATE FUNCTION [dbo].[fn_GetTotalWorkingHours] ( @DateFrom Datetime, @DateTo Datetime ) RETURNS DECIMAL(18,2) AS BEGIN DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2) SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo) -(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2) -CASE WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday' THEN 1 ELSE 0 END+CASE WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday' THEN 1 ELSE 0 END; SET @TotalTimeDiff = ( SELECT DATEDIFF(SECOND, ( SELECT CONVERT(TIME, @DateFrom) ), ( SELECT CONVERT(TIME, @DateTo) )) / 3600.0 ); RETURN (SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff) END GO
This function allows us to change out query above to:
select WorkItem.WorkItemId ,WorkItem.Created ,WorkItem.ResolvedDate ,DATEDIFF(HOUR, WorkItem.Created, WorkItem.ResolvedDate) as ATTR_base ,dbo.fn_GetTotalWorkingHours(WorkItem.Created, WorkItem.ResolvedDate) as ATTR_Ndlovu from ServiceManagement.dbo.WorkItem where WorkItem.ResolvedDate is not null
The new result set will return much better results, with generally lower ATTR values than normal. The general idea is that weekends are excluded, making your ATTR values more accurate. However, this assumes that all work days are 24 hours long.
I needed something better than a 24-hour work day, so I sought out to find something at Google again. And I failed, pretty badly. I found several similar 24-hour queries, some queries that use a lookup table with holidays, some 100+ line queries written by “Reddit’s finest” etc. And then I realized it would be faster to just modify Ndlovu’s query to only include certain work hours. First, I created three new variables, only two of which are manually-assigned. They could potentially be parameterized if desired.
DECLARE @StartOfDay TIME = '07:30:00' DECLARE @EndOfDay TIME = '17:00:00' DECLARE @maximumWorkDayTime INT = CAST(DATEDIFF(HOUR, @StartOfDay, @EndOfDay) as INT)
Next, I replaced the final Return statement from:
RETURN (SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff)
…to…
RETURN (@TotalWorkDays * @maximumWorkDayTime) + @TotalTimeDiff
This statement ensures that each day is a maximum of 11.5 hours (or 9 hours, or w/e) instead of 24 hours. This works pretty well. But it has the drawback of counting any transition from one day to the next as one full work day. For example, if an incident was opened on Thursday at 9pm, and was resolved on Friday at 9am, it would count one full work day (11.5 hours) and then add time from the start of the work day to the resolution time (7:30am to 9am) giving a total of 13 hours (11.5 + 1.5). So we add in a little math and get the finished function:
/* Function fn_Dust_GetTotalWorkingHours Created by Joivan This was originally built by Sifiso W. Ndlovu, https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/ . It has been modified to account for business hours, assuming each work day is 7:30am to 5pm, not 24 hours. It also accounts for partial end or beginning of days, such as when a date starts on Wed @ 4pm and ends Thursday @ 8am, which should be 1.5 hours. */ CREATE FUNCTION [dbo].[fn_Dust_GetTotalWorkingHours] ( @DateFrom Datetime, @DateTo Datetime ) RETURNS DECIMAL(18,2) AS BEGIN --DECLARE @DateFrom DATETIME='2014-12-10 14:00:00.523' --Manual testing only, not for use in the function --DECLARE @DateTo DATETIME='2014-12-18 10:00:00.783' --Manual testing only, not for use in the function DECLARE @StartOfDay TIME = '07:30:00' DECLARE @EndOfDay TIME = '17:00:00' DECLARE @maximumWorkDayTime INT = CAST(DATEDIFF(HOUR, @StartOfDay, @EndOfDay) as INT) DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2) SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo) - (DATEDIFF(WEEK, @DateFrom, @DateTo) * 2) - CASE WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday' THEN 1 ELSE 0 END + CASE WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday' THEN 1 ELSE 0 END - CASE WHEN CONVERT(TIME, @DateFrom) > CONVERT(TIME, @DateTo) THEN 1 --The start time (4pm) occured after the end time (9am), so don't count a full day for this instance. ELSE 0 END; SET @TotalWorkDays = CASE WHEN @TotalWorkDays < 0 THEN 0 ELSE @TotalWorkDays END; SET @TotalTimeDiff = ( SELECT CASE WHEN CAST(@DateFrom as TIME) > CAST(@DateTo as TIME) AND CAST(@DateFrom as TIME) > @EndOfDay THEN 0 WHEN CAST(@DateFrom as TIME) > CAST(@DateTo as TIME) THEN (DATEDIFF(SECOND, ( SELECT CONVERT(TIME, @DateFrom) ), @EndOfDay) / 3600.0) + (DATEDIFF(SECOND, @StartOfDay, CONVERT(TIME, @DateTo)) / 3600.0) --WHEN CAST(@DateFrom as TIME) > @EndOfDay THEN 0 WHEN CAST(@DateTo as TIME) > CAST(@DateFrom as TIME) AND CAST(@DateFrom as TIME) > @EndOfDay THEN 0 ELSE DATEDIFF(SECOND, ( CONVERT(TIME, @DateFrom) ), ( CONVERT(TIME, @DateTo) )) / 3600.0 END ); RETURN (@TotalWorkDays * @maximumWorkDayTime) + @TotalTimeDiff END GO /* Function fn_Dust_GetTotalWorkingHours End */
Next, you can use the function by calling:
select WorkItem.WorkItemId ,WorkItem.Created ,WorkItem.ResolvedDate ,DATEDIFF(HOUR, WorkItem.Created, WorkItem.ResolvedDate) as ATTR_base ,dbo.fn_GetTotalWorkingHours(WorkItem.Created, WorkItem.ResolvedDate) as ATTR_Ndlovu ,dbo.[fn_Dust_GetTotalWorkingHours](WorkItem.Created, WorkItem.ResolvedDate) as ATTR_Dust from ServiceManagement.dbo.WorkItem where WorkItem.ResolvedDate is not null
As an example, you can see some example results below:
Enjoy!