SQL WHERE子句结合CASE WHEN使用声明变量过滤历史事件日期

直接修改方案 #

你原有代码的问题出在WHERE子句中CASE表达式的用法错误，CASE用于返回具体值，不能直接返回判断表达式，也不能在WHERE子句中定义别名。直接替换原CASE相关的条件即可：

/* 
Event Dates:
2021-07-20
2021-08-02
2021-08-04
2021-08-05
*/

DECLARE @event_date DATE = '2021-08-04', @start_date DATE, @end_date DATE 

if @event_date  = '2021-07-20' BEGIN SET @start_date = '2021-07-15' SET @end_date = '2021-07-20' END
else if @event_date = '2021-08-02' BEGIN SET @start_date = '2021-07-28' SET @end_date = '2021-08-02' END
else if @event_date = '2021-08-04' BEGIN SET @start_date = '2021-07-30' SET @end_date = '2021-08-04' END
else if @event_date = '2021-08-05' BEGIN SET @start_date = '2021-07-31' SET @end_date = '2021-08-05' END

SELECT acct_num, dt, var1, var2, var3, var4, var5 
FROM TEST_DB 
WHERE dt BETWEEN @start_date AND @end_date 
-- 替换原有CASE逻辑的排除条件
AND NOT (
    (@event_date = '2021-08-04' AND dt = '2021-08-02')
    OR (@event_date = '2021-08-05' AND dt IN ('2021-08-02', '2021-08-04'))
)
AND acct_num = 1234

可扩展通用方案 #

如果后续需要新增更多事件日期，不用每次修改判断条件，可以把所有预设事件日期存入表变量，自动过滤早于当前@event_date的其他事件日期：

DECLARE @event_date DATE = '2021-08-05', @start_date DATE, @end_date DATE 
-- 存储所有预设事件日期
DECLARE @all_event_dates TABLE (event_dt DATE)
INSERT INTO @all_event_dates VALUES ('2021-07-20'),('2021-08-02'),('2021-08-04'),('2021-08-05')

if @event_date  = '2021-07-20' BEGIN SET @start_date = '2021-07-15' SET @end_date = '2021-07-20' END
else if @event_date = '2021-08-02' BEGIN SET @start_date = '2021-07-28' SET @end_date = '2021-08-02' END
else if @event_date = '2021-08-04' BEGIN SET @start_date = '2021-07-30' SET @end_date = '2021-08-04' END
else if @event_date = '2021-08-05' BEGIN SET @start_date = '2021-07-31' SET @end_date = '2021-08-05' END

SELECT acct_num, dt, var1, var2, var3, var4, var5 
FROM TEST_DB 
WHERE dt BETWEEN @start_date AND @end_date 
-- 自动排除所有早于当前事件日期的其他预设事件日期
AND dt NOT IN (SELECT event_dt FROM @all_event_dates WHERE event_dt < @event_date)
AND acct_num = 1234

效果验证 #

• 当@event_date为'2021-08-04'时，排除逻辑会过滤掉dt='2021-08-02'的记录，返回区间内剩余符合要求的行
• 当@event_date为'2021-08-05'时，排除逻辑会过滤掉dt为'2021-08-02'、'2021-08-04'的记录，完全符合预期

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