MS-SQL Work out next working day accounting for weekends and public holidays

ms-sql-calendar-working-days.sql

/*
  This was from another source, probably good old Stackoverflow! but capturing here for quick reference

  This script will create a calendar table, generate a stored proc to populate it working out weekends and also if you provide
  a set of holiday dates, it will add those too.

  The end result is a couple of select statements that allows you to pass a date (todays date as default), then add/subtract
  number of days to calculate the next working date based on the above weekends and/or holidays
*/
CREATE TABLE [dbo].[Calendar](
      [Date] [date] NULL,
      [Year] [int] NULL,
      [Month] [int] NULL,
      [Day] [int] NULL,
      [IsWeekend] [bit] NULL,
      [IsHoliday] [bit] NULL,
      [BusinessHoursStartTime] [time](7) NULL,
      [BusinessHoursEndTime] [time](7) NULL,
      [WorkingDaysOrder] [int] NULL
)
GO

CREATE PROCEDURE [dbo].[GenerateCalendar] 
AS
BEGIN
      SET NOCOUNT ON;

      SET DATEFIRST 1 -- 1: Monday, 7: Sunday

      DECLARE @StartDate DATE = '2019-01-01'
      DECLARE @EndDate DATE = '2019-12-31'

      DECLARE @HolidaysTbl as TABLE (HolidayDate date)
      INSERT INTO @HolidaysTbl(HolidayDate) VALUES ('2019-01-01'), ('2019-04-19'), ('2019-04-22'), ('2019-05-06'), ('2019-05-27'), ('2019-08-26'), ('2019-12-25'), ('2019-12-26');

      DELETE FROM Calendar;

      WITH GeneratedDates AS
      (
            SELECT
                  GeneratedDate = @StartDate

            UNION ALL

            SELECT
                  GeneratedDate = DATEADD(DAY, 1, G.GeneratedDate)
            FROM
                  GeneratedDates AS G
            WHERE
                  G.GeneratedDate < @EndDate
      ),
      SpecialDays AS
      (
            SELECT
                  Date = G.GeneratedDate,
                  Year = YEAR(G.GeneratedDate),
                  Month = MONTH(G.GeneratedDate),
                  Day = DAY(G.GeneratedDate),
                  IsWeekend = CASE WHEN DATEPART(WEEKDAY, G.GeneratedDate) IN (6, 7) THEN 1 ELSE 0 END,
                  IsHoliday = CASE WHEN H.HolidayDate IS NOT NULL THEN 1 ELSE 0 END
            FROM
                  GeneratedDates AS G
                  LEFT JOIN @HolidaysTbl AS H ON G.GeneratedDate = H.HolidayDate
      )
      INSERT INTO Calendar
      SELECT
            Date = S.Date,
            Year = S.Year,
            Month = S.Month,
            Day = S.Day,
            IsWeekend = S.IsWeekend,
            IsHoliday = S.IsHoliday,
            BusinessHoursStartTime = CASE WHEN S.IsHoliday = 0 AND S.IsWeekend = 0 THEN CONVERT(TIME, '09:00') END,
            BusinessHoursEndTIme = CASE WHEN S.IsHoliday = 0 AND S.IsWeekend = 0 THEN CONVERT(TIME, '18:00') END,
            WorkingDaysOrder = CASE 
                  WHEN S.IsHoliday = 0 AND S.IsWeekend = 0 
                  THEN ROW_NUMBER() OVER (
                        ORDER BY 
                        S.IsHoliday ASC,
                        S.IsWeekend ASC,
                        S.Date ASC) END
      FROM
            SpecialDays AS S
      OPTION
            (MAXRECURSION 0);
END
GO

EXEC [dbo].[GenerateCalendar]
GO

SELECT
      CAST(GETDATE() AS date) AS DateToday,
      (SELECT [Date] FROM [Calendar] WHERE [WorkingDaysOrder] = (SELECT MAX(WorkingDaysOrder) FROM Calendar WHERE [Date] <= CAST(GETDATE() AS date)) + 1) AS NextWorkingDay,
      (SELECT [Date] FROM [Calendar] WHERE [WorkingDaysOrder] = (SELECT MAX(WorkingDaysOrder) FROM Calendar WHERE [Date] <= CAST(GETDATE() AS date)) + 2) AS NextWorkingDayPlusOne
GO