Have you ever found yourself needing to calculate the next business day, taking into account weekends and holidays? In this article, we will explore a recursive function that can help you achieve this in SQL Server.
Let’s start by creating a table to hold the holidays:
CREATE TABLE [holiday] ( [holidayDate] [smalldatetime] NOT NULL, CONSTRAINT [PK_holidayDate] PRIMARY KEY CLUSTERED ([holidayDate]) )
Next, we will create the function fnGetNextBusinessDay
that takes a start date and the number of days to look forward:
CREATE FUNCTION fnGetNextBusinessDay (@startDate smalldatetime, @numDays int) RETURNS smalldatetime AS BEGIN DECLARE @nextBusDay smalldatetime DECLARE @weekDay tinyInt SET @nextBusDay = @startDate DECLARE @dayLoop int SET @dayLoop = 0 WHILE @dayLoop < @numDays BEGIN SET @nextBusDay = DATEADD(d, 1, @nextBusDay) -- first get the raw next day SET @weekDay = ((@@DATEFIRST + DATEPART(dw, @nextBusDay) - 2) % 7) + 1 -- always returns Mon=1 - can't use SET DATEFIRST in UDF -- % is the Modulo operator which gives the remainder -- of the dividend divided by the divisor (7) -- this allows you to create repeating -- sequences of numbers which go from 0 to 6 -- the -2 and +1 adjust the sequence start point (Monday) and initial value (1) IF @weekDay = 6 SET @nextBusDay = @nextBusDay + 2 -- since day by day Saturday = jump to Monday -- Holidays - function calls itself to find the next business day SELECT @nextBusDay = dbo.fnGetNextBusinessDay(@nextBusDay, 1) WHERE EXISTS (SELECT holidayDate FROM Holiday WHERE holidayDate = @nextBusDay) -- next day SET @dayLoop = @dayLoop + 1 END RETURN @nextBusDay END
Now, let’s dive into the details of this function. The first interesting thing is the use of Modulo to ensure that the function works regardless of the @@DATEFIRST setting. Modulo gives the remainder of one number divided by another. For example, 7 % 7 = 0, 9 % 7 = 2, 15 % 7 = 1, and so on. In this case, by adding the @@DATEFIRST value to the weekday value and applying modulo 7, we can create a sequence of numbers that represents the days of the week.
Here’s a chart of the numbers:
@@DATEFIRST plus Weekday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
---|---|---|---|---|---|---|---|
1 | 2 | 9 | 9 | 9 | 9 | 9 | 9 |
2 | 3 | 3 | 10 | 10 | 10 | 10 | 10 |
3 | 4 | 4 | 4 | 11 | 11 | 11 | 11 |
4 | 5 | 5 | 5 | 5 | 12 | 12 | 12 |
5 | 6 | 6 | 6 | 6 | 6 | 13 | 13 |
6 | 7 | 7 | 7 | 7 | 7 | 7 | 14 |
7 | 8 | 8 | 8 | 8 | 8 | 8 | 8 |
By taking (@@DATEFIRST + the weekday value) % 7, we always get the following sequence:
- Monday: 2
- Tuesday: 3
- Wednesday: 4
- Thursday: 5
- Friday: 6
- Saturday: 0
- Sunday: 1
From there, we subtract 2 from the @@DATEFIRST + the weekday value to start the sequence with 0 on Monday, and finally add 1 to that value so that Monday is always 1.
Now, let’s talk about the recursive part of this function. After calculating the next business day accounting for weekends, we need to handle holidays. If the next business day falls on a holiday, we need to find the next business day after that. This is where the magic of recursion comes in. The function calls itself with the next business day as the start date and 1 as the number of days to look forward. This process continues until a non-holiday business day is found.
In summary, recursive functions are a powerful tool when you need to perform the same calculation on the result of a previous calculation. Modulo can be used to create repeating sequences of numbers, which can be helpful in various scenarios. By combining these concepts, we can create a function that calculates the next business day, taking into account weekends and holidays.
I hope you found this article interesting and helpful. Feel free to leave any comments or questions below!