How do I combine working days of a month with annual holidays in SQL?

Question

I have a table that contains a list of public holidays for the current year. Now, the working days here are Monday-Saturday. So, I am trying to link the annual holidays table with my query in such a way that I get the list of Working Days for a given interval(Year, Month, Week etc) I have the code that gives me working days including Saturday but I am not able to subtract(link) the annual holidays.

Code for getting working days:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2017-12-01 10:00:00.000'
SET @EndDate = '2017-12-31 00:00:00.000'


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate))
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) AS WorkingDays

The table AnnualHolidays has the layout :

Id                  Date                                Description
----------- -----------------------                -------------------
50          2017-10-21 00:00:00.000                    Holiday 1
49          2017-10-20 00:00:00.000                    Holiday 2

So, Is there any way that I can subtract the annual holiday coming in the Interval of @StartDate and @EndDate such that I can get the actual Working days of the specified interval

score 1 · Accepted Answer · answered Dec 07 '17 at 16:53

One way would be to generate the valid range and than subtract the the holidays and count the result. I'm using a recursive function for the date range, but there are more ways to do this that can be more effective on larger ranges.

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2017-12-01 10:00:00.000'
SET @EndDate = '2017-12-31 00:00:00.000'


;WITH Dates AS (
        SELECT
            [Date] = @StartDate

        UNION ALL 

        SELECT
            [Date] = DATEADD(DAY, 1, [Date])
        FROM
            Dates
        WHERE
            Date <= @EndDate
) 
SELECT COUNT(*)
from
(
    SELECT
        [Date]
    FROM
        Dates
    WHERE
        DATENAME(dw, [Date]) != 'Sunday'

    EXCEPT

    select 
        [Date] 
    from                               
        AnnualHolidays 
) tbl

For some reason the annual holidays are not getting subtracted. It just shows the working days i.e removing Sundays but not removing annual holidays.. — Del Monte, Dec 08 '17 at 05:37
Did you manage to get it working? You can also do a `...not in(select date from AnnualHolidays)` — Magnus, Dec 08 '17 at 08:23

How do I combine working days of a month with annual holidays in SQL?

1 Answers1