One calculation that you are almost guaranteed to have to produce in your career as a T-SQL developer relates to the calculation of a turnaround time. This is often a key KPI for measuring the performance of both individuals and teams, particularly when the business operates within a service-oriented sector i.e. customer support, transportation, healthcare etc. Turnaround time calculation does not only refer to business metrics rather any activity (i.e. ordering a pizza) with a recorded start and an end time can have its own turnaround time calculated. In this article we evaluate different options for calculating a turnaround time including using DATEDIFF function, creating your own user-defined function (UDF) as well as an integration with SQL Server Master Data Services.
Generate a Calendar Date Table in SQL Server
The key to having an efficient turnaround time calculation is firstly having an accurate calendar date table. The internet is full of T-SQL scripts that demonstrate several ways of generating calendar date tables in SQL Server. For the purposes of this demo, I will create my calendar date table by generating a week’s worth of data using a WHILE statement, as shown in Script 1.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
DROP TABLE IF EXISTS [Demo].[dbo].[tblDate] CREATE TABLE [Demo].[dbo].[tblDate]( [DateKey] int not null, [Date] [date] NOT NULL, [IsWeekend] [tinyint] NOT NULL, [IsHoliday] [tinyint] NOT NULL DEFAULT(0), [Day] [tinyint] NOT NULL, [DayName] [varchar](10) NOT NULL, [Month] [tinyint] NOT NULL, [MonthName] [varchar](10) NOT NULL, [Year] [int] NOT NULL, ) ON [PRIMARY] DECLARE @DateEntry date = '12/24/2018' WHILE @DateEntry <= '12/31/2018' BEGIN insert into [Demo1].[dbo].[tblDate] ([DateKey],[Date],[IsWeekend], [IsHoliday] ,[Day],[DayName],[Month],[MonthName],[Year]) values (CONVERT(INT,CONVERT(VARCHAR(10), @DateEntry, 112)), @DateEntry ,CASE WHEN DATENAME(dw, @DateEntry) = 'Sunday' THEN 1 WHEN DATENAME(dw, @DateEntry) = 'Saturday' THEN 1 ELSE 0 END ,CASE WHEN CONVERT(INT,CONVERT(VARCHAR(10), @DateEntry, 112)) = 20181225 THEN 1 WHEN CONVERT(INT,CONVERT(VARCHAR(10), @DateEntry, 112)) = 20181226 THEN 1 ELSE 0 END ,Day(@DateEntry) ,DATENAME(dw, @DateEntry), Month(@DateEntry), DATENAME(Month, @DateEntry) ,Year(@DateEntry)) SET @DateEntry = DateAdd(d, 1, @DateEntry) END |
A preview of the data generated using Script 1 is shown in Figure 1.
Calculate Turnaround Time Using DATEDIFF Function
The simplest way of calculating a turnaround time in T-SQL is to use SQL Server’s built-in DATEDIFF function. Figure 2 shows the details of a technical support ticket that has been logged and assigned to Consultant X.
Using a DATEDIFF function in Script 2, we get a turnaround time of 7 days as shown in Figure 3.
|
1 2 3 4 5 6 7 |
SELECT [TechSupportID] ,[IssueDescription] ,[AssignedTo] ,[DateAssigned] ,[DateResolved] ,DATEDIFF(DAY,[DateAssigned],[DateResolved]) [TurnAroundTime] FROM [Demo].[dbo].[tblTechnicalSupport] |
One obvious discrepancy with relying on SQL Server’s built-in DATEDIFF function for calculating a turnaround time is the fact that the DATEDIFF function – as explained here – works by tracking the number of datepart (i.e. days, weeks etc.) boundaries crossed within a given date range. As a result, instead of returning a turnaround time of 1 day, the following statement returns a 0 because for the DATEDIFF function, there were zero-day boundaries crossed between the selected date range.
|
1 |
SELECT DATEDIFF(DAY,'2018-12-24','2018-12-24') [TurnAroundTime] |
One workaround that we could employ would be to either add a positive 1 to our end date parameter value or subtract -1 from the start date in our DATEDIFF expression, as shown in Script 4.
|
1 |
SELECT DATEDIFF(DAY,DATEADD(DAY,-1,'2018-12-24'),'2018-12-24') [TurnAroundTime] |
Another limitation with solely relying on a DATEFIDD function when calculating turnaround time is the fact that by default, weekends and holidays are included. However, we can always eliminate weekends by identifying the number of weeks between a given date range and then subtracting that number from the original DATEDIFF value, as shown in Script 5.
|
1 |
SELECT DATEDIFF(DAY,DATEADD(DAY,-1,'2018-12-24'),'2018-12-31') - (2 * DATEDIFF(WK,'2018-12-24','2018-12-31')) |
Removing weekends help reduces our original turnaround time from 7 days (returned in Figure 3) down to 6 days. Yet, we know that from our sample calendar date table (shown in Figure 1), we should further remove 2 days from the 6 days due to Christmas holidays. This following section demonstrates the removal of holidays from your turnaround time calculation.
Exclude Holidays in Turnaround Time Calculation Using Scalar-Valued Functions
Up until this point, your turnaround time calculation has been done in-line against the transactional table i.e. (dbo.tblTechnicalSupport). Yet, the identification and exclusion of holidays within a given a date range is a row-by-row operation that requires additional datasets or subqueries. There are several options for identifying and excluding holidays which includes setting up a lookup holiday table object that you could join back to your transactional table. My preferred approach involves creating a user defined function that could take the start and end dates as parameters and return a turnaround time. The benefit of this approach is that you don’t have to use the DATEDIFF function, which means you don’t have to – amongst other things – worry about handling the datepart boundary crossing issue inherent to SQL Server’s built-in DATEDIFF function.
Script 6 shows the definition of my sample scalar-valued function. You will notice in the definition of my function that instead of using the DATEDIFF function, my turnaround time calculation is now simply a count of calendar date entries that are neither weekends nor holidays.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE FUNCTION dbo.ufnGetTurnAroundTime(@startdate date, @enddate date) RETURNS int AS BEGIN DECLARE @ret int; SELECT @ret = count(*) FROM [Demo].[dbo].[tblDate] where [Date] between @startdate and @enddate and [IsWeekend] <> 1 and [IsHoliday] <> 1 IF (@ret IS NULL) SET @ret = 0; RETURN @ret; END; |
Using the newly created function, I have rewritten my SELECT statement against my dbo.tblTechnicalSupport table and the turnaround time value is correctly returned as 4 days.
|
1 2 3 4 5 6 7 |
SELECT [TechSupportID] ,[IssueDescription] ,[AssignedTo] ,[DateAssigned] ,[DateResolved] ,dbo.ufnGetTurnAroundTime([DateAssigned],[DateResolved]) [TurnAroundTime NEW] FROM [Demo].[dbo].[tblTechnicalSupport] |
At this point, all of my turnaround time values have actually been in days, but you can always represent the day value in a time format. Again, the internet is full of examples of how you can convert hours into time. Using an hour-to-time conversion example found here, I have multiplied the value returned from my scalar-valued function by 8 (typical number of hours in a work day). Obviously introducing the part that converts the turnaround time day value to time format does make the script look untidy which is why maybe you should consider actually moving that part inside the scalar-valued function script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT [techsupportid], [issuedescription], [assignedto], [dateassigned], [dateresolved], Replace(Cast(CONVERT(DECIMAL(10, 2), Cast(( dbo.Ufngetturnaroundtime([dateassigned], [dateresolved]) * 8 ) AS INT) + ( ( ( dbo.Ufngetturnaroundtime([dateassigned], [dateresolved]) * 8 ) - Cast(( dbo.Ufngetturnaroundtime([dateassigned], [dateresolved]) * 8 ) AS INT) ) * .60 )) AS VARCHAR), '.', ':') FROM [Demo].[dbo].[tbltechnicalsupport] |
Nevertheless, the execution of Script 8 shows that our turnaround time in actual time format of hh:mm.
An MDS Approach to Turnaround Time Calculation
As previously mentioned, the key to identifying and excluding holidays in a turnaround time calculation depends on setting up a lookup calendar date table that is well maintained. At the moment, my calendar date table can only be maintained by running UPDATE and INSERT scripts using T-SQL. However, you can eliminate such a dependency by moving the calendar date table into a SQL Server Master Data Services (MDS) instance. That way, you could have business users maintain the table accordingly ensuring that your focus remains on simply reading data out of that view for your turnaround time calculation.
Figure 6 shows a preview of my tblDate MDS entity that is used to store calendar date entries.
Following the creation of the MDS entity, I have updated my scalar-valued function to reference the newly created MDS entity as shown in Figure 7.
Another benefit of using MDS for turnaround time calculation is that you could easily change granular level from day to hour. This is because some people (i.e. consultants) don’t have a standard 9am-5pm/8-hour long workday. You could introduce an Hour field in your MDS table and have that table updated accordingly by a data steward too. Furthermore, with granular level set to hour, you no longer have to maintain holidays or weekends columns because all you need to do is just capture zero hours for days not worked (i.e. holidays and Sundays). This way, you solution is scalable – which it means it can be rolled out to any country and across time zones because you no longer have to spend time figuring out what days are that country’s public holidays etc.
Figure 7 shows an updated preview of the calendar date table that is based off an MDS entity. As it can be seen, I have dropped the IsHoliday and IsWeekend fields and replaced them with an Hours column.
I next updated my UDF function to rather SUM up the Hours column instead of doing a row count.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
ALTER FUNCTION [dbo].[ufnGetTurnAroundTime](@startdate date, @enddate date) RETURNS int AS BEGIN DECLARE @ret int; SELECT @ret = SUM([Hours]) FROM [MDS2014].[mdm].[vw_tblDate] where [Date] between @startdate and @enddate IF (@ret IS NULL) SET @ret = 0; RETURN @ret; END; |
Summary
In this article, we’ve looked at several ways for calculating the turnaround time. The benefit of using the DATEDIFF function is that you don’t have to rely on creating additional lookup tables or user-defined functions. Yet, using scalar-valued functions give you the flexibility to identify and exclude holidays from your turnaround time calculation script. Finally, MDS driven entities can make your calculation script both scalable and flexible.
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu