计算工作日时如何将应返回0的结果从NULL替换为0？

我在计算两个日期之间的工作日数，部分结果返回NULL是符合预期的，但有些本该返回0的情况（比如两个日期相等时）也显示了NULL。以下是我的查询语句、源数据、当前结果和期望结果，麻烦帮我看看哪里出问题了，该怎么修正？

我的查询语句 #

SELECT 
    ROW_NUMBER() OVER (ORDER BY Date_key asc) BusinessDaysID,
    BSDAYS * CASE WHEN B.ClosingDate > B.ApprovalDate THEN -1 ELSE 1 END FinaldDateCount 
FROM DIM_DATE B 
CROSS APPLY ( 
    SELECT NULLIF(COUNT(*),0) BSDAYS 
    FROM CALENDAR 
    WHERE BSDAYS >= CASE WHEN B.ClosingDate > B.ApprovalDate THEN B.ApprovalDate ELSE B.ClosingDate END 
      AND BSDAYS < CASE WHEN B.ClosingDate > B.ApprovalDate THEN B.ClosingDate ELSE B.ApprovalDate END 
) R1

源表数据 #

DIM_DATE 示例数据 #

CALENDAR 示例数据 #

BSDAYS
2018-04-27
2018-04-30
2018-05-01
2018-05-22
2018-05-23

当前查询结果 #

期望结果 #

问题分析 #

你的问题出在NULLIF(COUNT(*),0)这个函数上：

• 当ClosingDate和ApprovalDate相等时，WHERE子句的条件变成BSDAYS >= X AND BSDAYS < X，这个范围不会匹配任何数据，所以COUNT(*)返回0；
• NULLIF(0,0)会把0转换成NULL，导致最终的FinalDateCount变成NULL，而这正是你需要返回0的场景；
• 对于有NULL值的行（比如ClosingDate或ApprovalDate为NULL），WHERE子句的条件会因为涉及NULL变成UNKNOWN，COUNT(*)同样返回0，NULLIF也转成NULL，这部分是符合你预期的，所以我们需要区分两个日期都不为NULL但相等和存在NULL日期这两种情况。

修正后的查询语句 #

SELECT 
    ROW_NUMBER() OVER (ORDER BY Date_key asc) BusinessDaysID,
    CASE 
        -- 只要有一个日期为NULL，返回NULL（符合你的预期）
        WHEN B.ClosingDate IS NULL OR B.ApprovalDate IS NULL THEN NULL
        -- 两个日期相等时直接返回0
        WHEN B.ClosingDate = B.ApprovalDate THEN 0
        -- 其他情况计算工作日数，保留正负逻辑
        ELSE R1.BSDAYS * CASE WHEN B.ClosingDate > B.ApprovalDate THEN -1 ELSE 1 END
    END FinaldDateCount 
FROM DIM_DATE B 
CROSS APPLY ( 
    SELECT COUNT(*) BSDAYS 
    FROM CALENDAR 
    WHERE BSDAYS >= CASE WHEN B.ClosingDate > B.ApprovalDate THEN B.ApprovalDate ELSE B.ClosingDate END 
      AND BSDAYS < CASE WHEN B.ClosingDate > B.ApprovalDate THEN B.ClosingDate ELSE B.ApprovalDate END 
) R1

这个修正后的查询会：

1. 保留原有逻辑中对NULL日期返回NULL的处理；
2. 对两个日期相等的情况直接返回0；
3. 其他正常的日期范围计算，依然返回正确的正负工作日数。

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