Calculating time intervals in working days using a calendar table

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

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.