SQL中CASE WHEN搭配DATEDIFF统计文档数结果异常排查

问题根因 #

你的查询返回错误结果，核心是两处写法错误：

• COUNT()函数逻辑误用
  COUNT(列/表达式)的计数规则是只统计表达式返回值不为NULL的行。你写的CASE语句中，不满足时间条件的分支返回了0，而0属于非NULL值，会被COUNT正常计入统计，这就是为什么两条本不满足条件的记录都被算进去，始终返回结果2。
• 未排除未来时间的脏数据
  结合你给出的测试数据和当前时间2022-05-27 9:20:00，两条记录的[Assigned Date]都晚于当前时间，DATEDIFF(MINUTE, [Assigned Date], GETDATE())计算出的时间差为负数，本不满足>60/>120的筛选条件，就是因为上述COUNT的逻辑错误才被误统计。如果业务场景是统计已分配超过N小时未拣货的单据，这类未来时间的异常数据本身也应该被过滤掉。

修正方案 #

方案1：修正CASE分支返回值（保留COUNT写法） #

将不满足条件的分支返回值改为NULL，COUNT会自动忽略这些行，同时增加过滤条件排除未来时间的脏数据：

SELECT 
    COUNT(CASE 
              WHEN DATEDIFF(MINUTE, [Assigned Date], GETDATE()) > 60 
                  THEN [Document No.] 
                  ELSE NULL
          END) AS [Yet to Pick > 1 hour], 
    COUNT(CASE 
              WHEN DATEDIFF(MINUTE, [Assigned Date], GETDATE()) > 120 
                  THEN [Document No.] 
                  ELSE NULL
          END) AS [Yet to Pick > 2 hours]
FROM 
    tb_name
WHERE 
    ([Shipment] LIKE '%AIR%' OR [Shipment] LIKE '%COURIER%')
    AND [Assigned Date] <= GETDATE()

方案2：改用SUM实现条件计数（逻辑更直观） #

如果觉得COUNT需要返回NULL的规则不好记，可以改用SUM做条件求和，满足条件记1、不满足记0，最终求和结果就是符合条件的记录数，不容易出错：

SELECT 
    SUM(CASE 
              WHEN DATEDIFF(MINUTE, [Assigned Date], GETDATE()) > 60 
                  THEN 1
                  ELSE 0
          END) AS [Yet to Pick > 1 hour], 
    SUM(CASE 
              WHEN DATEDIFF(MINUTE, [Assigned Date], GETDATE()) > 120 
                  THEN 1
                  ELSE 0
          END) AS [Yet to Pick > 2 hours]
FROM 
    tb_name
WHERE 
    ([Shipment] LIKE '%AIR%' OR [Shipment] LIKE '%COURIER%')
    AND [Assigned Date] <= GETDATE()

修正后的语句不会再将不满足条件的记录误计入统计，当测试数据中仅1条记录符合时间差要求时，即可返回你预期的正确结果1。

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