SQL日期范围筛选：排除含Video/Face-to-Face的整月数据

我需要排除所有存在Video或Face-to-Face记录的整月数据，仅保留这两类记录均不存在的月份。目前使用NOT EXISTS语句可实现需求，但添加日期范围筛选时，由于范围内存在单个符合排除条件的实例，导致所有数据被排除。

示例数据 #

原测试代码 #

以下两行注释为测试语句，用于查看执行效果：

SELECT 
    c1
    ,c2
    ,C3

FROM  a1

WHERE
not exists (SELECT * FROM a1 as B WHERE b.c1 = a1.c1 and (b.c3= 'Face-to-Face' or b.c3 = 'Video') )
--and a1.c2 between '2022-06-01' and '2022-06-30')    
--and a1.c2 = b.c2)

and c2 between '2022-01-01' and '2022-12-30'

解决方案 #

原代码的问题在于NOT EXISTS未按月份关联，它检查的是整个表中该c1是否存在任何违规记录，而非当前行所在月份是否存在违规记录。以下两种方法可实现按月份筛选的需求：

方法1：先筛选合法月份再关联查询

SELECT 
    a1.c1,
    a1.c2,
    a1.c3
FROM a1
INNER JOIN (
    -- 找出c1对应的、无Video/Face-to-Face记录的月份
    SELECT 
        c1,
        DATEFROMPARTS(YEAR(c2), MONTH(c2), 1) AS month_start
    FROM a1
    GROUP BY c1, YEAR(c2), MONTH(c2)
    HAVING SUM(CASE WHEN c3 IN ('Video', 'Face-to-Face') THEN 1 ELSE 0 END) = 0
) valid_months ON a1.c1 = valid_months.c1 
               AND DATEFROMPARTS(YEAR(a1.c2), MONTH(a1.c2), 1) = valid_months.month_start
WHERE a1.c2 BETWEEN '2022-01-01' AND '2022-12-30';

方法2：修正NOT EXISTS的月份关联逻辑

SELECT 
    c1,
    c2,
    c3
FROM a1
WHERE NOT EXISTS (
    SELECT 1 
    FROM a1 AS b 
    WHERE b.c1 = a1.c1
      AND YEAR(b.c2) = YEAR(a1.c2)
      AND MONTH(b.c2) = MONTH(a1.c2)
      AND b.c3 IN ('Video', 'Face-to-Face')
)
AND a1.c2 BETWEEN '2022-01-01' AND '2022-12-30';

说明 #

两种方法均以c1+月份为维度，先锁定无违规记录的月份组合，再筛选对应数据。添加日期范围后，只会排除范围内存在违规记录的月份，不会误删所有符合日期条件的数据。

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