使用CASE语句标记需排除的特定医疗理赔数据问题求助

问题背景 #

• Table A：存储诊所全部理赔数据
• Table B：筛选出的特定年份、特定服务（服务码为1234）的理赔数据
• 核心需求：对Table B的每一行进行标记，若该行对应的会员在当日（同一天）的Table A中存在除1234外的其他服务，则标记EXCLUSION_FLAG=1（需排除），判断逻辑为行级而非整会员级。

现有尝试代码 #

SELECT DISTINCT 
B.*,
(CASE
    WHEN EXISTS
        (SELECT 1
        FROM TableA -- Table with all the claims --
        WHERE A.MemberID = B.MemberID 
        AND (A.Date = B.Date)
        AND A.Service != 1234) -- For claims for services I do NOT want, but are on the same day --
THEN 1
ELSE 0
END) AS EXCLUSION_FLAG
FROM TableB B -- Table with specific claims --

问题分析 #

现有代码标记不符合预期，大概率是以下原因：

1. DISTINCT的干扰：如果Table B存在重复行，DISTINCT会先合并行再计算标记，导致行级判断逻辑错位，原本应标记1的行被错误合并为0。
2. 日期匹配精度问题：若Date字段是带时间戳的类型（如TIMESTAMP_NTZ），直接用A.Date = B.Date会因时分秒不一致导致匹配失败，误判为当日无其他服务。

修正方案 #

方案1：移除DISTINCT（Table B无重复行时优先使用） #

如果Table B已经是去重后的特定服务数据，直接去掉DISTINCT，同时修正日期匹配逻辑，确保按“天”而非精确时间匹配：

SELECT 
    B.*,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM TableA A
            WHERE A.MemberID = B.MemberID 
              AND DATE_TRUNC('day', A.Date) = DATE_TRUNC('day', B.Date) -- 按自然日匹配，兼容带时间戳的日期字段
              AND A.Service != 1234
        ) THEN 1
        ELSE 0
    END AS EXCLUSION_FLAG
FROM TableB B

方案2：保留DISTINCT（Table B有重复行时使用） #

若必须保留去重逻辑，先计算行级标记再执行DISTINCT，避免判断逻辑被干扰：

SELECT DISTINCT
    B.*,
    EXCLUSION_FLAG
FROM (
    SELECT 
        B.*,
        CASE
            WHEN EXISTS (
                SELECT 1
                FROM TableA A
                WHERE A.MemberID = B.MemberID 
                  AND DATE_TRUNC('day', A.Date) = DATE_TRUNC('day', B.Date)
                  AND A.Service != 1234
            ) THEN 1
            ELSE 0
        END AS EXCLUSION_FLAG
    FROM TableB B
) AS sub_query

验证步骤 #

1. 检查日期字段类型：执行DESCRIBE TABLE TableA;和DESCRIBE TABLE TableB;，确认Date列是否为时间戳类型，若为是则必须用DATE_TRUNC或DATE()提取日期部分。
2. 手动验证异常行：针对标记错误的行，单独执行以下查询确认是否存在当日其他服务：

SELECT 1
FROM TableA A
WHERE A.MemberID = '异常会员ID' 
  AND DATE_TRUNC('day', A.Date) = DATE_TRUNC('day', '异常行日期')
  AND A.Service != 1234

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