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

Possible duplicate of [Oracle query to get the number of business days between 2 dates, excluding holidays](https://stackoverflow.com/questions/54443802/oracle-query-to-get-the-number-of-business-days-between-2-dates-excluding-holid) — Barbaros Özhan, Jan 31 '19 at 18:38
Possible duplicate of [How can I exclude weekends and holidays in sql server query](https://stackoverflow.com/questions/36021519/how-can-i-exclude-weekends-and-holidays-in-sql-server-query) — Ken White, Jan 31 '19 at 18:38
Hi, the Oracle syntax are different than SQL Server, for example SQL Server doesn't have TRUNC( end_date, 'IW' ) — faujong, Jan 31 '19 at 18:54
Hi, https://stackoverflow.com/questions/36021519/how-can-i-exclude-weekends-and-holidays-in-sql-server-query doesn't calculate the number of business days in between 2 dates — faujong, Jan 31 '19 at 19:01
You do realize that in your example, `Date1` is greater than `Date2` and will return nothing. — SS_DBA, Jan 31 '19 at 19:20
Use a calendar table. There are hundreds if not thousands of examples of how to use these. — Sean Lange, Jan 31 '19 at 19:34

jean · Accepted Answer · 2019-01-31T19:25:07.867

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)

Thank you, Jean. I used your solution and updated it a bit (I edited my question with the final solution). — faujong, Jan 31 '19 at 19:40

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

1 Answers1

Linked