Calculating time intervals in working days using a calendar table

SQL

All data in this post is fake data, generated artificially.

I sometimes want to look at waiting times or turnaround times measured only in working days. This might be when thinking about a service which is only open on working days. Sometimes I’ve also seen service-level agreements specify performance metrics measured in working days.

A calendar table - a table in which each record represents a single calendar day - gives you a neat way of doing that.

The code looks like this:

SELECT
    te.RequestedDate
    ,requestedday.[Day.of.Week] AS 'Day of Week Requested'
    ,te.CompletedDate
    ,completedday.[Day.of.Week] AS 'Day of Week Completed'
    ,te.[Turnaround (calendar days)]
    ,(SELECT COUNT(*)
    FROM lkp_calendar dy
    WHERE dy.[Working.Day] = 1 --working days only
    AND dy.[Date] > te.RequestedDate
    AND dy.[Date] < te.CompletedDate)
        AS 'Turnaround (working days)'

FROM turnaroundexample te
LEFT JOIN lkp_calendar requestedday
    ON CAST(te.RequestedDate AS date) = requestedday.[Date]
LEFT JOIN lkp_calendar completedday
    ON CAST(te.completedDate AS date) = completedday.[Date]

WHERE te.RequestedDate >= '2020 MAR 01'
AND te.RequestedDate < '2020 MAR 08'

ORDER BY te.RequestedDate
ABCDEFGHIJ0123456789
RequestedDate
<S3: POSIXct>
Day of Week Requested
<chr>
CompletedDate
<S3: POSIXct>
2020-03-01 00:44:10Sun2020-03-05 01:12:40
2020-03-01 01:43:45Sun2020-03-03 02:16:07
2020-03-01 02:15:07Sun2020-03-02 08:31:51
2020-03-01 02:19:27Sun2020-03-20 13:24:29
2020-03-01 02:29:42Sun2020-03-04 15:10:51
2020-03-01 02:31:14Sun2020-03-01 21:15:30
2020-03-01 02:58:53Sun2020-03-01 10:26:55
2020-03-01 03:16:52Sun2020-03-01 04:22:21
2020-03-01 03:45:48Sun2020-03-03 17:32:41
2020-03-01 03:57:44Sun2020-03-03 23:10:05
2020-03-01 04:01:55Sun2020-03-04 08:12:50
2020-03-01 04:09:34Sun2020-03-01 13:28:08
2020-03-01 04:29:58Sun2020-03-03 18:34:49
2020-03-01 04:35:26Sun2020-03-01 14:21:36
2020-03-01 04:53:46Sun2020-03-08 21:50:44
2020-03-01 06:45:07Sun2020-03-06 23:56:25
2020-03-01 06:59:13Sun2020-03-02 03:55:42
2020-03-01 07:13:39Sun2020-03-12 14:06:48
2020-03-01 08:04:22Sun2020-03-02 03:41:41
2020-03-01 08:15:51Sun2020-03-02 23:13:13
Next
123456
...
24
Previous
1-20 of 461 rows | 1-3 of 6 columns

The key is the correlated sub-query, which returns a row for each day in the calendar table (lkp_calendar) that’s labelled as a working day between the request date and the completion date. The dates in the calendar table don’t have a time, so SQL Server interprets them as midnight on the date in question. Therefore the code counts the midnights between the two dates, as long as the following day is a working day.

In particular, requests made on a Friday and completed on Saturday or Sunday have a turnaround of 0 days, while requests made on a Sunday and completed on a Monday have a turnaround of 1 day. The behaviour for these edge cases can be adjusted as desired in the sub-query using CASTS and choosing between strict and non-strict inequalities.

There are lots of alternative approaches out there for counting working days. I like this approach because it’s a clean way to account for bank holidays. I also find it easier to reason about our calendar table, which is very familiar to me, than I do to reason about some of the more obscure date functions in T-SQL.