SQL Server查询以获取两个日期之间的工作日数，不包括节假日 [英] SQL Server query to get the number of business days between 2 dates, excluding holidays

问题描述

我们正在使用SQL Server.

We are using SQL Server.

在我们的CASE WHEN语句中，我需要检查两个日期之间的天数是否大于3个工作日(因此不包括周末和节假日).

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

说我有一个假期日历表，该表的HolidayDates列包含所有假期，例如:12/25/2018、12/31/2018等.

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.

假日日期2018年12月25日2018年12月31日所以，如果

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

日期1 = 1/2/19(星期三)

Date1 = 1/2/19 (Wednesday)

Date2 = 18/27/18(星期四)

Date2 = 12/27/18 (Thursday)

日期1和日期2之间的工作日数为3天(12/27、12/28和12/31).

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 ?

谢谢.

编辑后回答:

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

推荐答案

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

退货

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

(3 row(s) affected)

这篇关于SQL Server查询以获取两个日期之间的工作日数，不包括节假日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！