Published on

September 23, 2005

Understanding Recursive Functions in SQL Server

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 WeekdayMondayTuesdayWednesdayThursdayFridaySaturdaySunday
12999999
2331010101010
344411111111
45555121212
5666661313
677777714
78888888

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!

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.