SQL实现排除周末与节假日的N天交易金额求和问题

原始数据表 #

需求说明 #

统计7月数据，计算每个日期对应的5个工作日的金额总和，统计范围排除规则：

• 排除周六、周日周末
• 排除2021年7月5日（07/05/2021）假期，允许硬编码假期

期望输出结果 #

参考建表及数据插入语句 #

create table TRANSACTIONS (
  AccountID int,
  Date date,
  Amount int
)

insert into TRANSACTIONS values (123, '07/02/2021', 2000)
insert into TRANSACTIONS values (123, '07/09/2021', 9000)
insert into TRANSACTIONS values (123, '07/15/2021', 500)
insert into TRANSACTIONS values (123, '07/20/2021', 500)
insert into TRANSACTIONS values (123, '07/28/2021', 500)

现有实现代码（已实现跳过周末逻辑） #

SELECT AccountId, Date,
(
  SELECT SUM(Amount) 
  FROM TRANSACTIONS h2
  WHERE 
    h1.AccountID = h2.AccountID and 
    DATEPART(WEEKDAY, h2.Date) not in (1, 7) and
    h2.Date between h1.Date AND DATEADD(d, 6, h1.Date)
) as SumAmount
FROM TRANSACTIONS h1

修改方案 #

因为允许硬编码假期，直接在子查询的WHERE条件中新增一行过滤指定假期即可，该日期会和周末一样被视为非工作日，不计入统计范围，修改后的代码如下：

SELECT AccountId, Date,
(
  SELECT SUM(Amount) 
  FROM TRANSACTIONS h2
  WHERE 
    h1.AccountID = h2.AccountID and 
    DATEPART(WEEKDAY, h2.Date) not in (1, 7) and
    h2.Date != '07/05/2021' -- 新增硬编码排除指定假期
    h2.Date between h1.Date AND DATEADD(d, 6, h1.Date)
) as SumAmount
FROM TRANSACTIONS h1

如果你的数据库日期格式要求是YYYY-MM-DD，把排除条件改为h2.Date != '2021-07-05'即可。

内容的提问来源于stack exchange，提问作者user13468392