T-SQL Way of Calculating Next n Business Days (Including Nominated Public Holidays and Weekends)

Introduction

This article introduce a new approach of calculating next N business days's date, provided we know the start date and the N number. It utilizes a DimDate table similar to the data warehouse, to mark non-business days.

Background

Calculating next N business days is always a challenge to many T-SQL developers. There are a few SQL approaches published online but most of them look after weekend dates only. In many circumstances, local public holidays such as Christmas Day, Boxing Day, ANZAC Day (in Australia) will have to be taken into consideration and excluded from being calculated as Business Days.

Using the Code

1. Next N Calendar Days Approach

T-SQL has built DATEADD function which does this perfectly.

Next N Calendar Days Date Calculation can be achieved as follows:

DECLARE @dateStart DATETIME = '2016-04-12 09:30:00'; 
DECLARE @n INT = 10; 

SELECT DATEADD(DAY, @n, @dateStart) AS CalendarDaysAdd; -- Calculates next n Calendar days

2. Problemed Next N Business Days Approach

To record non-business days' dates, we created a SQL table to store the dates that are not business days.

The table looks like below. It stores all weekend dates as well as local public holidays.

NOMINATED_DATE
2016-03-28
2016-04-02
2016-04-03
2016-04-09
2016-04-10
2016-04-16
2016-04-17
2016-04-23
2016-04-24
2016-04-25
2016-04-30
2016-05-01
2016-05-02

So the T-SQL would look like below. It follows the approach of exclude (non-business days) and then compensate to extend.

DECLARE @intNoHolidays INT; 
DECLARE @dateStart DATETIME = '2016-04-12 09:30:00'; 
DECLARE @n INT = 10; 

SELECT @intNoHolidays = COUNT(1) FROM dbo.NOMINATED_DATES 
WHERE    NOMINATED_DATE BETWEEN @dateStart AND DATEADD(DAY, @n, @dateStart)
-- calculate number of holidays/weekends in this range
;

SELECT DATEADD(DAY, @n + @intNoHolidays, @dateStart) AS WrongBusinessDaysAdd; 
-- wrong results because there are more weekend/holidays in the additional period

Here, we calculate the number of non-business days during the period in the normal range of calendar days calculation and then compensate those number of days to extend the range.

The problem is, the extended days range may contain new non-business days and they should not be calculated in. To address the problem, we may have to re-determine if there are non-business days in the extended range. We may be able to use loops to check until there are no more non-business days in the latest extended range, however this is definitely is not a neat approach in the world of T-SQL.

3. A Working Next N Business Days Approach

This solution works well for the problem described.

First, we added two columns to the existing DimDate table, namely IsHoliday (BIT) and WorkDaySeqNo (INT).

After that, update the IsHoliday Column, assign 0 for Business Days and assign 1 for non-Business Days.

Then use T-SQL Window-Function to update the WorkDaySeqNo column with incrementing integers for Business Days only. Non-business day's dates will remain null (or -1 if you wish). The starting point of the SeqNo doesn't really matter, in the example below, I used 2016-01-04 as the first Business Day.

DateKey       Date        IsHoliday    WorkDaySeqNo
20160412    2016-04-12    0                69
20160413    2016-04-13    0                70
20160414    2016-04-14    0                71
20160415    2016-04-15    0                72
20160416    2016-04-16    1                NULL
20160417    2016-04-17    1                NULL
20160418    2016-04-18    0                73
20160419    2016-04-19    0                74
20160420    2016-04-20    0                75
20160421    2016-04-21    0                76
20160422    2016-04-22    0                77
20160423    2016-04-23    1                NULL
20160424    2016-04-24    1                NULL
20160425    2016-04-25    1                NULL
20160426    2016-04-26    0                78
20160427    2016-04-27    0                79
20160428    2016-04-28    0                80
20160429    2016-04-29    0                81
20160430    2016-04-30    1                NULL
20160501    2016-05-01    1                NULL

The code in function is as follows, it looks after the time part as well:

DECLARE @dateStart DATETIME = '2016-04-12 09:30:00'; -- input param, start date and time
DECLARE @n INT = 10;                                 -- input param, next N business days

DECLARE @time TIME = CAST(@dateStart AS TIME);       -- intermedia param, stores time part
DECLARE @dueDateSeq INT;                             -- intermedia param, to find target WorkDaySeqNo

SELECT @dueDateSeq = WorkDaySeqNo + @n 
FROM dbo.DimDate 
WHERE [Date] = CAST(@dateStart AS DATE); 

-- this is to match the start date in DimDate Table, anchor the starting point
-- then by adding the @n on top of it, calculate the destination SeqNo integer, by design, DimDate
-- table WorkDaySrqNo column has excluded all non-business dates
 
SELECT CAST((
              SELECT [Date] 
              FROM [dbo].[DimDate] 
              WHERE WorkDaySeqNo = @dueDateSeq
            ) AS DATETIME) +                         -- convert the WorkDaySeqNo int to a valid date
       CAST(@time AS DATETIME);                      -- combine with the time part for final DATETIME

By the above design, the routine skips the non-business dates in the DimDate table and converts the dates manipulation to an integer calculation which ensures the performance and most importantly, avoids any iteration and IF clause in T-SQL.

4. Case When Start Date Is a Non-Business Day?

Depends on the business rule, if it requires to start counting using the nearest Business Day's date, if started on a non-Business Date, simply add the following T-SQL after extracting and storing the time part of the Start DateTime.

SELECT @dateStart = MIN([Date])
FROM dbo.DimDate 
WHERE [Date] >= CAST(@dateStart AS DATE) AND IsHoliday = 0;    

The above code converts the Start Date to the first Business Day's date after, if Start Date falls on a public holiday. You can also customize the time part, e.g. 08:00 AM of the next Business Day's date.

Points of Interest

Create a SQL function to calculate next N Business Day's date in T-SQL.