多条件Case语句问题：日期与状态（服务订单重开场景）

问题场景 #

服务订单（SO）数据中，日期字段对应Queue、Complete、Canceled等多种状态。当Queue日期晚于Complete日期（即SO完成后被重开）时，尝试用Case语句适配该场景，但当前SQL返回两行数据：一行是正确的NULL完成日期，一行是原始完成日期。

原始数据 #

期望输出 #

当前使用的SQL代码 #

SELECT
M.BI_SO_NBR,
SQ.start_queue_date,
SQ.CANCL_DATE,
case when (ta.bi_work_event_cd ='COMP' and comp_date < start_queue_date) then null else SQ.COMP_DATE  end as comp_date2
FROM
BI_SO_MASTER M
LEFT OUTER JOIN BI_SO_DET D ON M.BI_SO_NBR = D.BI_SO_NBR
LEFT OUTER JOIN BI_WRKFLW_TASKS T ON D.BI_WRKFLW_KEY = T.BI_WRKFLW_KEY
LEFT OUTER JOIN BI_TASK_ASG_SCHED A ON T.BI_WRKFLW_TASKS_KEY = A.BI_WRKFLW_TASK_KEY
LEFT OUTER JOIN BI_TASK_ACT TA oN T.BI_WRKFLW_TASKS_KEY = TA.BI_WRKFLW_TASKS_KEY
LEFT OUTER JOIN 
     (SELECT  
          TA.BI_WRKFLW_TASKS_KEY, 
          max(CASE WHEN (ta.bi_work_event_cd ='START') THEN ta.bi_event_dt_tm
            ELSE (CASE WHEN (ta.bi_work_event_cd = 'QUEUE') THEN ta.bi_event_dt_tm 
            else NULL END) END) AS start_queue_date ,
          max(CASE WHEN(  ta.bi_work_event_cd ='COMP') THEN ta.bi_event_dt_tm
          else NULL ENd) AS COMP_DATE,
          max(CASE WHEN(ta.bi_work_event_cd ='CANCL') THEN ta.bi_event_dt_tm
          else NULL END) AS CANCL_DATE
      FROM bi_task_act ta 
      GROUP BY ta.bi_wrkflw_tasks_key ) SQ 
   ON (T.BI_WRKFLW_TASKS_KEY = SQ.BI_WRKFLW_TASKS_KEY)  

问题根源在于直接关联了BI_TASK_ACT原始表，导致每条任务活动记录生成一行数据，同时子查询SQ已完成日期聚合，无需重复关联。此外，需调整判断逻辑并确保按SO聚合，保证单条结果输出。

修正后的SQL #

SELECT
    M.BI_SO_NBR AS "SO #",
    SQ.start_queue_date AS "Queue Date",
    -- 最新Queue日期晚于Comp日期时返回NULL，否则返回Comp日期
    CASE WHEN SQ.start_queue_date > SQ.COMP_DATE THEN NULL ELSE SQ.COMP_DATE END AS "Comp Date",
    SQ.CANCL_DATE
FROM
    BI_SO_MASTER M
LEFT OUTER JOIN BI_SO_DET D ON M.BI_SO_NBR = D.BI_SO_NBR
LEFT OUTER JOIN BI_WRKFLW_TASKS T ON D.BI_WRKFLW_KEY = T.BI_WRKFLW_KEY
LEFT OUTER JOIN BI_TASK_ASG_SCHED A ON T.BI_WRKFLW_TASKS_KEY = A.BI_WRKFLW_TASK_KEY
-- 移除多余的BI_TASK_ACT关联，避免重复行
LEFT OUTER JOIN 
     (SELECT  
          TA.BI_WRKFLW_TASKS_KEY, 
          -- 合并START和QUEUE条件，取最新日期作为Queue Date
          MAX(CASE WHEN ta.bi_work_event_cd IN ('START', 'QUEUE') THEN ta.bi_event_dt_tm END) AS start_queue_date ,
          MAX(CASE WHEN ta.bi_work_event_cd = 'COMP' THEN ta.bi_event_dt_tm END) AS COMP_DATE,
          MAX(CASE WHEN ta.bi_work_event_cd = 'CANCL' THEN ta.bi_event_dt_tm END) AS CANCL_DATE
      FROM bi_task_act ta 
      GROUP BY ta.bi_wrkflw_tasks_key ) SQ 
   ON T.BI_WRKFLW_TASKS_KEY = SQ.BI_WRKFLW_TASKS_KEY
-- 按SO及聚合后的日期字段分组，确保单条结果
GROUP BY M.BI_SO_NBR, SQ.start_queue_date, SQ.COMP_DATE, SQ.CANCL_DATE;

关键修正点 #

• 移除多余关联：删掉与BI_TASK_ACT的直接关联，子查询SQ已聚合所需日期数据，避免因原始表多记录产生重复行。
• 简化日期计算：用IN条件合并START和QUEUE的判断，逻辑更清晰，同时通过MAX取最新日期。
• 调整判断逻辑：在外层直接比较聚合后的最新Queue日期和Comp日期，决定是否返回NULL。
• 添加聚合分组：通过GROUP BY确保每个SO只返回一条结果，消除关联其他表可能产生的重复行。

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