基于Calendar表计算Request_Stages阶段工作日时长及查询修复

问题根源 #

你的查询存在两个核心问题：

• 使用INNER JOIN：只有能匹配到STG_ACCR_DATE中工作日的请求阶段才会被保留，工作日时长为0的阶段直接被过滤；
• 起止日期相同的场景：如果该日期是休息日或不在日历表中，INNER JOIN会丢失这条记录，且COUNT(DAT.holiday)会返回0，不符合你期望返回1的要求。

修正后的SQL查询 #

SELECT 
    Req.requestID, 
    Req.STAGE_ID,
    -- 统计工作日数量，无匹配时返回0；同时处理起止同天的特殊逻辑
    COALESCE(
        SUM(CASE WHEN DAT.OFF_DAY = 0 THEN 1 ELSE 0 END),
        0
    ) + 
    -- 如果起止日期相同，强制加1（若该日是工作日则会重复计数，所以要减去重复的部分）
    CASE 
        WHEN Req.DATE_FROM = Req.DATE_TO THEN 1 
        ELSE 0 
    END - 
    CASE 
        WHEN Req.DATE_FROM = Req.DATE_TO AND DAT.OFF_DAY = 0 THEN 1 
        ELSE 0 
    END AS duration
FROM REQUEST Req
-- 改用LEFT JOIN保留所有请求阶段
LEFT JOIN [dbo].[STG_ACCR_DATE] DAT
    ON DAT.DATE >= Req.DATE_FROM
    AND DAT.DATE <= Req.DATE_TO -- 修正原查询的笔误：FDATE改为DATE
GROUP BY Req.requestID, Req.STAGE_ID, Req.DATE_FROM, Req.DATE_TO
ORDER BY Req.requestID, Req.STAGE_ID

简化版本（日历表包含所有日期时） #

如果你的STG_ACCR_DATE表覆盖了所有可能的日期（无缺失），可以用更简洁的写法：

SELECT 
    Req.requestID, 
    Req.STAGE_ID,
    -- 仅统计工作日的数量，无匹配则返回0
    COALESCE(COUNT(CASE WHEN DAT.OFF_DAY = 0 THEN DAT.DATE END), 0) AS duration
FROM REQUEST Req
LEFT JOIN [dbo].[STG_ACCR_DATE] DAT
    ON DAT.DATE BETWEEN Req.DATE_FROM AND Req.DATE_TO
GROUP BY Req.requestID, Req.STAGE_ID
ORDER BY Req.requestID, Req.STAGE_ID

关键调整点 #

1. LEFT JOIN替换INNER JOIN：确保所有请求阶段都能出现在结果中，哪怕没有匹配的工作日；
2. COALESCE处理NULL：当没有匹配的工作日记录时，把聚合函数返回的NULL转换成0；
3. 起止同天的逻辑修正：如果你要求无论该日是否为工作日都计1，通过CASE语句补充计数并避免重复；
4. 语法错误修正：原查询中Req.STAGE_ID后缺少逗号，且DAT.FDATE应为DAT.DATE（疑似笔误）。

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