SQL Server query to get the number of business days between 2 dates, excluding holidays

Question

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 WHEN end_date - start_date > 3  THEN 0  --> this need to exclude 
    weekend and holidays
WHEN CODE = 1 THEN 1
WHEN CODE =2 THEN 2
ELSE 3
END AS MyColumn

Say I have a holiday calendar table that has column HolidayDates that contains all the holidays, for ex: 12/25/2018, 12/31/2018, etc.

HolidayDates 12/25/2018 12/31/2018 So, if

Date1 = 1/2/19 (Wednesday)

Date2 = 12/27/18 (Thursday)

The number of business days in between Date1 and Date2 is 3 days (12/27, 12/28 and 12/31).

The above query will get the number of business days including weekends and holidays.

How do I also exclude weekends and holidays in the query ?

Thank you.

Edited with answer:

select start_date, end_date,
datediff(day, mt.start_date, mt.end_date) datediff,
(select
 (datediff(wk, mt.start_date, mt.end_date) )
 +(case when datename(dw, mt.start_date) = 'sunday'   then 1 else 0 end)
 +(case when datename(dw, mt.end_date)   = 'saturday' then 1 else 0 end)
 ) weekend,
(select count(*) from HolidayDates hd
where hd.holydayDate between mt.start_date and mt.end_date
 ) as [holydays (not weekends)],
datediff(day, mt.start_date, mt.end_date)
-(select
(datediff(wk, mt.start_date, mt.end_date) )
+(case when datename(dw, mt.start_date) = 'sunday'   then 1 else 0 end)
+(case when datename(dw, mt.end_date)   = 'saturday' then 1 else 0 end)
) * 2
-(select count(*) from HolidayDates hd
 where hd.holydayDate between mt.start_date and mt.end_date
)
as diff
from MyTable mt

Answer 1

create table MyTable
(
 start_date date not null,
 end_date date not null,
 code int not null
)
GO

create table HolidayDates
(
   holydayDate date not null,
   holydayDescription varchar(100) not null
)
GO

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

insert into 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], 
  (
    select 
      count(*) 
    from 
      HolidayDates hd 
    where 
      hd.holydayDate between mt.start_date 
      and mt.end_date 
      and DATEPART(WEEKDAY, hd.holydayDate) between 2 
      and 6
  ) as [holydays (not weekends) ], 
  (
    select 
      (
        datediff(wk, mt.start_date, mt.end_date) * 2
      ) +(
        case when datename(dw, mt.start_date) = 'sunday' then 1 else 0 end
      ) +(
        case when datename(dw, mt.end_date) = 'saturday' then 1 else 0 end
      )
  ) as weekends, 
  case when datediff(day, mt.start_date, mt.end_date) -(
    select 
      (
        datediff(wk, mt.start_date, mt.end_date) * 2
      ) +(
        case when datename(dw, mt.start_date) = 'sunday' then 1 else 0 end
      ) +(
        case when datename(dw, mt.end_date) = 'saturday' then 1 else 0 end
      )
  ) -(
    select 
      count(*) 
    from 
      HolidayDates hd 
    where 
      hd.holydayDate between mt.start_date 
      and mt.end_date 
      and DATEPART(WEEKDAY, hd.holydayDate) between 2 
      and 6
  ) > 3 then 0 --> this need to exclude weekend and holidays
  when mt.code = 1 then 1 when mt.code = 2 then 2 else 3 end as mycolumn 
from 
  MyTable mt

RETURNS

total days  holydays (not weekends) weekends    mycolumn
----------- ----------------------- ----------- -----------
7           2                       2           3
61          2                       18          0
9           2                       2           0

(3 row(s) affected)