TSQL - Add business days to date, not including holidays

Question

I am working on a situation where I have a priority list that is associated with a specific amount of business days.

For example, if I had todays date of 2017-01-26 and needed to add 5 business days to it, it would return 2017-02-02.

To mix in some added functionality, I have a table of company defined holidays that it needs to exclude. If I select 5 business days from 2017-02-16, it would see that US Presidents day falls on 2017-02-20 and would skip that, making the 5th business day 2017-02-24.

I found another example on SO of this which I am trying to adapt to my needs. The problem is, I don't exactly know what is happening, only that my additions are not resulting in what I expect.

Reference Post: https://stackoverflow.com/a/12862675/2628921

ALTER FUNCTION [dbo].[findBusinessDayAfter]
(@date DATETIME, @days INT)
RETURNS DATE
AS
BEGIN
    RETURN (SELECT thedate
            FROM   (SELECT dateadd(d, v.day, CAST (@date AS DATE)) AS thedate,
                           row_number() OVER ( ORDER BY v.day) AS rn
                    FROM   (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36), (37), (38), (39), (40)) AS v(day)
                           LEFT OUTER JOIN
                           holidays AS h
                           ON h.holidayDate = dateadd(d, v.day, CAST (@date AS DATE))
                    WHERE  h.holidayDate IS NULL
                           AND LEFT(datename(dw, dateadd(d, v.day, CAST (@date AS DATE))), 1) <> 'S') AS x
            WHERE  @days = rn);
END

In the original example given, they are using (1) - (10). However, I have cases where I will need to add 35-40 business days to a date, so I adjusted the query accordingly to allow me to go up to that.

I have found that when I try to run certain dates with business days, it is returning a null value and I am not sure why.

-- Multiple Holidays (Day before Thanksgiving)
SELECT dbo.findBusinessDayAfter('2017-11-05', 35) -- Returns NULL

I am not exactly sure what is happening in the original code snippet that would prevent me from being able to increase their default 10 days to my 30+ days.

Any thoughts on how this could be adapted to fit my use case?

Why not create a calendar table and do something like this? https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/ — dadde, Jan 26 '17 at 20:44

Søren Kongstad · Answer 1 · 2017-01-27T13:36:30.470

I tried running the interior of your SP by doing this:

DECLARE @holidays TABLE(holidayDate DATE);
INSERT INTO @holidays
       SELECT *
       FROM(VALUES('20170103')) f(b);
DECLARE @date DATETIME= '20170101', @days INT= 1;

I then run the interior select statement

SELECT DATEADD(d, v.day, CAST(@date AS DATE)) AS thedate,
       ROW_NUMBER() OVER(ORDER BY v.day) AS rn
FROM(VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36), (37), (38), (39), (40)) AS v(day)
    LEFT OUTER JOIN @holidays AS h ON h.holidayDate = DATEADD(d, v.day, CAST(@date AS DATE))
WHERE h.holidayDate IS NULL
      AND LEFT(DATENAME(dw, DATEADD(d, v.day, CAST(@date AS DATE))), 1) <> 'S'

This lists days that are business days, starting with the date supplied, and ending at the business day closest to the calendar date of @date+45 days. All Holidays and weekends are removed from the list. With the values i Entered there are 29 business days from Jan 1 2017 and february 15, which is 45 calendar days after the January 1. So this is your problem, you want to go 35 business days, but there simply aren't that many business days in the next 45 calendar days.

I hope this makes it clear what happens in your code, and why it fails. Now a quick fix would be to look forward more calendar days. Here I cross join with your 45 day 3 times, and calculate a running total from 1 to 45*45*45-1 days ahead

    SELECT DATEADD(d, v1.day+45*(v2.day-1)+45*45*(v3.day-1), CAST(@date AS DATE)) AS thedate,
           ROW_NUMBER() OVER(ORDER BY v1.day+45*(v2.day-1)+45*45*(v3.day-1)) AS rn
    FROM
       (VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36), (37), (38), (39), (40)) AS v1(day)
cross join     
       (VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36), (37), (38), (39), (40)) AS v2(day)
cross join     
       (VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36), (37), (38), (39), (40)) AS v3(day)
        LEFT OUTER JOIN @holidays AS h ON h.holidayDate = DATEADD(d, v1.day+45*(v2.day-1)+45*45*(v3.day-1), CAST(@date AS DATE))
    WHERE h.holidayDate IS NULL
          AND LEFT(DATENAME(dw, DATEADD(d, v1.day+45*(v2.day-1)+45*45*(v3.day-1), CAST(@date AS DATE))), 1) <> 'S'

This gives you the possibility to go thousands of days ahead. It might not be the most elegant solution, but it should solve your problem.

TSQL - Add business days to date, not including holidays

1 Answers1