A recursive function for calculating next business day

By Robert Scholl

Overview

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.

Scripts

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

Details

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:

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.

Summary

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.