Sql Server Query To Get The Number Of Business Days Between 2 Dates, Excluding Holidays

We are using SQL Server. In our CASE WHEN statement, I need to check if the number of days between the 2 dates are > 3 business days (so excluding weekends and holidays). CASE W

Solution 1:

createtable MyTable
(
 start_date datenotnull,
 end_date datenotnull,
 code intnotnull
)
GO

createtable HolidayDates
(
   holydayDate datenotnull,
   holydayDescription varchar(100) notnull
)
GO

insertinto MyTable
values
 ('2018-12-25','2019-01-01',101)
,('2018-12-01','2019-01-31',102)
,('2018-12-24','2019-01-02',103)
GO

insertinto HolidayDates
values
 ('2018-12-25', 'xmas')
,('2019-01-01', 'Reveillon')
GO

In the below query you can see the how the columns are calculated.

[holydays (not weekends)]: get all holydays form your table are not also weekends (so they are not counted twice).

weekends: get weekends in the period.

The rest os the columns can be self-explanatory

Disclaimer, you can simplify this a bit, it's just an example query in how to use

DATEPART

DATEDIFF

DATNAME

select 
  datediff(day, mt.start_date, mt.end_date) as [total days], 
  (
    selectcount(*) 
    from 
      HolidayDates hd 
    where 
      hd.holydayDate between mt.start_date 
      and mt.end_date 
      and DATEPART(WEEKDAY, hd.holydayDate) between2and6
  ) as [holydays (not weekends) ], 
  (
    select 
      (
        datediff(wk, mt.start_date, mt.end_date) *2
      ) +(
        casewhen datename(dw, mt.start_date) ='sunday'then1else0end
      ) +(
        casewhen datename(dw, mt.end_date) ='saturday'then1else0end
      )
  ) as weekends, 
  casewhen datediff(day, mt.start_date, mt.end_date) -(
    select 
      (
        datediff(wk, mt.start_date, mt.end_date) *2
      ) +(
        casewhen datename(dw, mt.start_date) ='sunday'then1else0end
      ) +(
        casewhen datename(dw, mt.end_date) ='saturday'then1else0end
      )
  ) -(
    selectcount(*) 
    from 
      HolidayDates hd 
    where 
      hd.holydayDate between mt.start_date 
      and mt.end_date 
      and DATEPART(WEEKDAY, hd.holydayDate) between2and6
  ) >3then0--> this need to exclude weekend and holidayswhen mt.code =1then1when mt.code =2then2else3endas mycolumn 
from 
  MyTable mt

RETURNS

total days  holydays (not weekends) weekends    mycolumn
----------- ----------------------- ----------- -----------
72236121809220

(3 row(s) affected)