A recursive function for calculating next business day
By Robert Scholl
The other day I found myself needing to come up with a way to calculate the next business day including taking into account holidays. A recursive function turned out to be just the thing to use.
Another challenge was handling different @@DATEFIRST settings. The problem was that in a user defined function you cannot use SET DATEFIRST. To get around this I used the Modulo function. I'll show you the scripts first and then go into the details.
To start with, you'll need to create a table to hold the holidays:
CREATE TABLE [holiday] ( [holidayDate] [smalldatetime] NOT NULL , CONSTRAINT [PK_holidayDate] PRIMARY KEY CLUSTERED ( [holidayDate] ) )
Next you'll need to create the function:
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO 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 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
The first interesting thing about this script is the use of Modulo to make sure the function works no matter what @@DATEFIRST is set at. For those of you not familiar with Modulo, it gives the remainder of one number divided by another. So for example: 7 % 7 = 0, 9 % 7 = 2, 15 % 7 = 1 etc. I use this function all the time with Crystal reports to create a greenbar effect. If you take the record number modulo 2 you'll get 0 when it's even and 1 when it's odd. In this case, if you added the @@DATEFIRST value to the weekday value, it resulted in a sequence of numbers that was ripe to have modulo 7 applied to it.
Here's a chart of the numbers:
|@@DATEFIRST plus Weekday||1||2||3||4||5||6||7|
Taking (@@DATEFIRST + the weekday value) % 7 always returns the following sequence:
From there, the next thing to do was 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 was always 1. If you would like to explore modulo sequences, it's very easy to do using the MOD function in Microsoft Excel (this is what I did).
Now to the recursive part of this procedure. I had taken care of calculating the next business day accounting for weekends and began to work on the holidays part of the procedure. The first step was to check if the next business day was a holiday and therefore had an entry in the holiday table. If there was one, then I had to go to the next day. However it couldn't simply be the next day since the next day could also be a weekend or a holiday. It had to be the next business day. That's when the light went off. Have the function call itself! I just hard coded 1 as the number of days to look forward and then called the function.
The magic question with recursive functions is : "Do I have to do a calculation and then do the same calculation on the result?" If you ever find yourself asking that question, you've got a great candidate for a recursive function. The magic question with Modulo is "Do I have a repeating series of numbers?" If so, Modulo may be the answer. I hope you found this little piece interesting.