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:
[php]
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
[/php]
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:
[php]
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
[/php]
This function allows us to change out query above to:
[php]
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
[/php]
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.
[php]
DECLARE @StartOfDay TIME = ’07:30:00′
DECLARE @EndOfDay TIME = ’17:00:00′
DECLARE @maximumWorkDayTime INT = CAST(DATEDIFF(HOUR, @StartOfDay, @EndOfDay) as INT)
[/php]
Next, I replaced the final Return statement from:
[php]
RETURN (SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff)
[/php]
…to…
[php]
RETURN (@TotalWorkDays * @maximumWorkDayTime) + @TotalTimeDiff
[/php]
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:
[php]
/*
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 */
[/php]
Next, you can use the function by calling:
[php]
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
[/php]
As an example, you can see some example results below:
Enjoy!