sql-server - 检查某些日期是否适合开始和结束日期范围以及匹配日期的总数

Question

我在其中一个 SQL Server 表中有一个Start DateandEnd Date列。我有一个银行假日日期列表。我需要测试每行的每个开始和结束日期范围，以检查有多少假期属于该范围以及这些假期的总数。

我目前正在通过CASE WHEN以下声明/假期日期来实现这一目标

select 
    a.StartDate, 
    a.EndDate,
    (CASE when '2017-01-01' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2017-04-14' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2017-04-17' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2017-05-22' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2017-07-01' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2017-08-07' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2017-09-04' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2017-10-09' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2017-11-11' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2017-12-25' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2017-12-26' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2018-01-01' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2018-03-30' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2018-04-02' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2018-05-21' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2018-07-01' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2018-08-06' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2018-09-03' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2018-10-08' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2018-11-11' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2018-12-25' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2018-12-26' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2019-01-01' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2019-04-19' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2019-04-22' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2019-05-20' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2019-07-01' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2019-08-05' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2019-09-02' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2019-10-14' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2019-11-11' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2019-12-25' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2019-12-26' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2020-01-01' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2020-04-10' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2020-04-13' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2020-05-18' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2020-07-01' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2020-08-03' between a.StartDate and a.EndDate then 1 else 0  end)+
    (CASE when '2020-09-07' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2020-10-12' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2020-11-11' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2020-12-25' between a.StartDate and a.EndDate then 1 else 0 end)+
    (CASE when '2020-12-26' between a.StartDate and a.EndDate then 1 else 0 end)
from
    (select '2017-01-01' as StartDate, '2019-12-31' as EndDate) a

有更好的方法吗？SQL 可以为每个 START 和 END 日期范围测试一行中有多少个假日日期相交？

标签： sql-server