Adding x work days onto a date in SQL Server?

I’m a bit confused if there is a simple way to do this.

I have a field called receipt_date in my data table and I wish to add 10 working days to this (with bank holidays).

I’m not sure if there is any sort of query I could use to join onto this table from my original to calculate 10 working days from this, I’ve tried a few sub queries but I couldn’t get it right or perhaps its not possible to do this. I didn’t know if there was a way to extract the 10th rowcount after the receipt date to get the calendar date if I only include ‘Y’ into the WHERE?

Any help appreciated.

Answer

This is making several assumptions about your data, because we have none. One method, however, would be to create a function, I use a inline table value function here, to return the relevant row from your calendar table. Note that this assumes that the number of days must always be positive, and that if you provide a date that isn’t a working day that day 0 would be the next working day. I.e. adding zero working days to 2021-09-05 would return 2021-09-06, or adding 3 would return 2021-09-09. If that isn’t what you want, this should be more than enough for you to get there yourself.

CREATE FUNCTION dbo.AddWorkingDays (@Days int, @Date date) 
RETURNS TABLE AS
RETURN
    WITH Dates AS(
        SELECT CalendarDate,
               WorkingDay
        FROM dbo.CalendarTable
        WHERE CalendarDate >= @Date)
    SELECT CalendarDate
    FROM Dates
    WHERE WorkingDay = 1
    ORDER BY CalendarDate
    OFFSET @Days ROWS FETCH NEXT 1 ROW ONLY;
GO

--Using the function

SELECT YT.DateColumn,
       AWD.CalendarDate AS AddedWorkingDays
FROM dbo.YourTable YT
     CROSS APPLY dbo.AddWorkingDays(10,YT.DateColumn) AWD;