How can I exclude weekends and holidays in SQL Server query

Question

I'm creating a query which will adjust the date excluding holidays and weekends.

Example data:

Adjusted Date | Adjusted Date(Excluding Holidays and weekends)

02/06/16 | 02/09/16

On my example, The date is a weekend and adjusted date becomes Feb 9 because Feb 8 is a holiday, so it needs to adjust so that the adjusted date would be a working day. Currently, I have a separated table of all the weekends and holidays in a fiscal year.

select  case when (
               select   count(dbo.WeekendsHoliday.[Weekends & Holidays])
               from     dbo.WeekendsHoliday
               where    dbo.WeekendsHoliday.[Weekends & Holidays] 
                    = case when convert(time, [Time Received]) > convert(time, '5:00:00 PM') 
                           then dateadd(day, 1, [Date Received]) 
                           else [Date Received] 
                      end
              ) > 0 
         then case (datename(DW, 
                             case when convert(time, [Time Received]) > convert(time, '5:00:00 PM') 
                                  then dateadd(day, 1, [Date Received])
                                  else [Date Received] 
                             end))
                when 'Saturday'
                then dateadd(day, 2, 
                             case when convert(time, [Time Received]) > convert(time, '5:00:00 PM') 
                                  then dateadd(day, 1, [Date Received])
                                  else [Date Received] 
                             end)
                else dateadd(day, 1, 
                             case when convert(time, [Time Received]) > convert(time, '5:00:00 PM') 
                                  then dateadd(day, 1, [Date Received])
                                  else [Date Received] 
                             end)
              end
    end as [Adjusted Date Excluding holidays and weekends]

What happens here is if the holiday is 2 consecutive days (Thursday and Friday), adjusted date would be Saturday which is still not valid because it's a weekend.

Adjusted date is an alias here

Answer 1

I have created very simpler code which will exclude holidays and saturday sunday while adding days in your date. Firstly you have to create holiday table for that then you can do with below code. I hope you will get your result with my code as I have created this and its working properly.

    ;WITH Numbers AS
(
    SELECT 1 AS value
    UNION ALL
    SELECT value + 1 AS value
    FROM Numbers
    WHERE Numbers.value <= 99
)
SELECT FinalTable.FromDate,FinalTable.AddedDays,FinalTable.AdjustedDate 
FROM (
    SELECT Final.*,ROW_Number() OVER (ORDER BY (SELECT NULL)) AS AddedDays 
    FROM (
        SELECT tbl.FromDate,CASE WHEN DATENAME(dw,tbl.AdjustedDate) = 'Saturday' THEN 0 
                                 WHEN DATENAME(dw,tbl.AdjustedDate) = 'Sunday' THEN 0
                                 WHEN tbl.AdjustedDate in (SELECT Holiday_Date FROM Holiday) THEN 0
                            ELSE 1 END AS LogicNumber ,tbl.days, tbl.AdjustedDate 
        FROM (
            SELECT @FromDate AS FromDate, DATEADD(DAY,num,@FromDate) AS AdjustedDate, num AS days 
            FROM ( 
                  SELECT ROW_Number() OVER (ORDER BY (SELECT NULL)) AS num  FROM Numbers
                 ) t
            WHERE num <= 100
             ) tbl 
        )Final 
        WHERE LogicNumber = 1 
    )FinalTable 
    WHERE AddedDays = @days

Lets Say @FromDate = '2017-12-30' And 1 January 2018 is holiday which is present in Holiday Table and we have to add 10 days i.e. @days = 10

OUTPUT

FromDate    | AddedDays | AdjustedDate
2017-12-30  | 10        | 2018-01-15

Answer 2

I suggest to create a function that recursively verify the next working day based on the table that contains weekends and holidays. The advantage of this approach is that it is a reusable function whenever you need it.

This function receives the date and time. (Based on the code in your question) if the time is after 5pm, adds a day. After, continues checking if the date is not within weekends or holidays until find the next working day:

CREATE FUNCTION dbo.adjustedDate(@dateReceived DATETIME, @timeReceived TIME)
RETURNS DATETIME
AS
BEGIN
    DECLARE @adjustedDate DATETIME = @dateReceived

    -- Verify time to add 1 day to @adjustedDate
    IF @timeReceived IS NOT NULL
        IF @timeReceived > CONVERT(TIME, '5:00:00 PM')
             SET @adjustedDate = DATEADD(DAY, 1, @adjustedDate)

    -- Continue adding 1 day to @adjustedDate recursively until find one date that is not a weekend or holiday
    IF EXISTS(SELECT [Weekends & Holidays]
                FROM dbo.WeekendsHoliday
                WHERE [Weekends & Holidays] = @adjustedDate)                
        SET @adjustedDate = dbo.adjustedDate(DATEADD(DAY, 1, @adjustedDate), NULL)

    RETURN @adjustedDate
END

Answer 3

SQL Fiddle Demo

SELECT MIN(allDays.dte)
FROM (
      SELECT '2015-01-01' + INTERVAL ones.a + 10*tens.a + 100*hundred.a DAY dte
      FROM
       (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) ones,
       (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) tens,
       (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) hundred
      WHERE '2015-01-01' + INTERVAL ones.a + 10*tens.a + 100*hundred.a DAY  <  '2016-01-01'
     ) allDays
LEFT JOIN holidays H
   ON allDays.dte = H.holydate
WHERE 
     H.holydate IS NULL
 AND allDays.dte >= '2015-12-12'  -- HERE go your Source DATE

OUTPUT

| MIN(allDays.dte) |
|------------------|
|       2015-12-14 |  --Because 12 and 13 are holidays

EXPLAIN

First you need create a list for allDays. Here I generate a subquery to list all days from 2015, you will need adapt to large ranges.

SELECT '2015-01-01' + INTERVAL ones.a + 10*tens.a + 100*hundred.a DAY dte
      FROM
       (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) ones,
       (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) tens,
       (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) hundred
      WHERE '2015-01-01' + INTERVAL ones.a + 10*tens.a + 100*hundred.a DAY  <  '2016-01-01'

Then you perform a left join to see if a DATE is holiday

LEFT JOIN holidays H
   ON allDays.dte = H.holydate

Finally just choose the smaller date who isnt holiday

WHERE H.holydate IS NULL           -- NULL mean doesnt have a match in the holiday table
  AND allDays.dte >= '2015-12-12'  -- HERE go your Source DATE

Answer 4

this takes the dates in your WeekendsHoliday table and finds the next day that's not in the same table.

then you left join to the result to get the "Next Day" if the date from your table is in the WeekendsHoliday table

DECLARE @WeekendsHoliday TABLE ([Weekends & Holidays] DATETIME)
INSERT INTO @WeekendsHoliday VALUES
('2016-03-05'),
('2016-03-06'),
('2016-03-07'),
('2016-03-12'),
('2016-03-13');

DECLARE @Schedule TABLE ([WorkDay] DATETIME)
INSERT INTO @Schedule VALUES
('2016-03-02'),
('2016-03-03'),
('2016-03-05'),
('2016-03-07'),
('2016-03-08'),
('2016-03-11'),
('2016-03-12');     

WITH RecursiveCTE AS
(
    SELECT
        [Weekends & Holidays],
        DATEADD(d, 1, [Weekends & Holidays]) AS [Next Day]
    FROM
        @WeekendsHoliday
    UNION ALL   
    SELECT 
        cte.[Weekends & Holidays],
        DATEADD(d, 1, [Next Day])
    FROM 
        RecursiveCTE cte
    WHERE 
        [Next Day] IN (SELECT [Weekends & Holidays] FROM @WeekendsHoliday)
),
AggregateCTE AS (
    SELECT 
        [Weekends & Holidays], 
        MAX([Next Day]) [Next Day] 
    FROM 
        RecursiveCTE 
    GROUP BY 
        [Weekends & Holidays]
)
SELECT  
    s.WorkDay,
    COALESCE(cte.[Next Day], s.WorkDay) AS [Adjusted Date Excluding holidays and weekends]
FROM 
    @Schedule s
    LEFT JOIN AggregateCTE cte ON s.[WorkDay] = cte.[Weekends & Holidays]

you use INNER JOIN AggregateCTE instead of LEFT JOIN AggregateCTE if you only want to see dates that are adjusted. i'd also recommend filtering the WITH RecursiveCTE cte by a start date and end date range if possible.