Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Finding the Next Business Day Recursively

By Rob Scholl,

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:

@@DATEFIRST plus Weekday    1       2       3       4       5       6       7   
Monday 2 9 9 9 9 9 9
Tuesday 3 3 10 10 10 10 10
Wednesday 4 4 4 11 11 11 11
Thursday 5 5 5 5 12 12 12
Friday 6 6 6 6 6 13 13
Saturday 7 7 7 7 7 7 14
Sunday 8 8 8 8 8 8 8

Taking (@@DATEFIRST + the weekday value) % 7 always returns the following sequence:

Monday2
Tuesday3
Wednesday  4
Thursday5
Friday6
Saturday0
Sunday1

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.

Total article views: 12831 | Views in the last 30 days: 9
 
Related Articles
FORUM

SET DATEFIRST SQL Server

How to change datefirst for SQL Server...

ARTICLE

Finding the Correct Weekday Regardless of DateFirst

In this article, learn how to get the correct weekday regardless of the DateFirst setting on your se...

FORUM

Holidays

I need to find a way to skip holidays for a scheduled job.

SCRIPT

Format Phone Number function

Function to format phone numbers entered as text.

FORUM

SET DATEFIRST option

Behavioural Difference of SET DATEFIRST option in SQL 2000 and SQL 2005

Tags
advanced querying    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones