基于日历表计算GRM续签请求平均工作日：SQL查询求助

没问题，我来帮你一步步搞定这个需求！咱们拆解成几个核心环节，确保逻辑准确且易于维护：

1. 先明确核心筛选规则 #

首先要把符合条件的请求挑出来，同时排除明显异常的数据：

• 上月（二月）完成的请求（这里推荐用通用的上月筛选逻辑，避免跨年或当前月份的问题）
• ApprovalRequiredFrom 严格等于 GRM renewal
• 排除日期为空、或者dateStageChangedToPendingApproval晚于dateApprovalReceived的异常数据（这种情况计算天数没有意义）

2. 利用日历表计算工作日天数 #

你的Calendar_Date日历表是关键，假设这个表有标记工作日的字段（比如IsWorkday，1代表工作日，0代表周末/节假日），我们可以通过关联日历表来精准统计两个日期之间的有效工作日数。

3. 最终计算平均值 #

把每个符合条件的请求的工作日数算出来后，直接求平均即可，注意要避免整数除法的问题（比如把数值转成小数再计算）

完整SQL查询代码 #

WITH ValidRequests AS (
    -- 筛选符合条件的请求，排除异常值
    SELECT 
        RequestID,
        dateStageChangedToPendingApproval,
        dateApprovalReceived
    FROM Requests
    WHERE 
        -- 通用的上月筛选逻辑，不管当前是哪个月都能准确匹配上月数据
        dateCompleted >= DATEFROMPARTS(
            DATEPART(year, DATEADD(month, -1, GETDATE())),
            DATEPART(month, DATEADD(month, -1, GETDATE())),
            1
        )
        AND dateCompleted < DATEFROMPARTS(
            DATEPART(year, GETDATE()),
            DATEPART(month, GETDATE()),
            1
        )
        AND ApprovalRequiredFrom = 'GRM renewal'
        -- 排除异常值：日期非空且变更日期早于/等于批准日期
        AND dateStageChangedToPendingApproval IS NOT NULL
        AND dateApprovalReceived IS NOT NULL
        AND dateStageChangedToPendingApproval <= dateApprovalReceived
        -- 可选：如果有其他异常判断（比如天数超过合理范围），可以加在这里
        -- AND DATEDIFF(day, dateStageChangedToPendingApproval, dateApprovalReceived) <= 30
),
RequestWorkdayCounts AS (
    -- 计算每个请求的工作日天数
    SELECT 
        vr.RequestID,
        COUNT(c.Calendar_Date) AS WorkdayCount
    FROM ValidRequests vr
    JOIN Calendar c
        ON c.Calendar_Date BETWEEN vr.dateStageChangedToPendingApproval AND vr.dateApprovalReceived
        AND c.IsWorkday = 1 -- 只统计工作日
    GROUP BY vr.RequestID
)
-- 计算平均工作日天数
SELECT AVG(CAST(WorkdayCount AS DECIMAL(10,2))) AS AvgApprovalWorkdays
FROM RequestWorkdayCounts;

关键细节说明 #

• 日期筛选的通用性：代码里用DATEADD和DATEFROMPARTS来获取上月的起止日期，不管当前是3月还是次年1月，都能准确筛选出上一个自然月的数据，比直接写month=2更灵活。
• 日历表的适配：如果你的日历表没有IsWorkday字段，也可以自己判断周末+排除节假日，比如把JOIN条件改成：

  ON c.Calendar_Date BETWEEN vr.dateStageChangedToPendingApproval AND vr.dateApprovalReceived
  AND DATEPART(weekday, c.Calendar_Date) NOT IN (1,7) -- 排除周六周日（注意不同数据库的weekday编号可能不同）
  AND c.Calendar_Date NOT IN (SELECT HolidayDate FROM Holidays) -- 如果有单独的节假日表
  

• 异常值扩展：如果还有其他异常情况（比如天数为0或者过大），可以在ValidRequests里添加额外的过滤条件，比如DATEDIFF(day, dateStageChangedToPendingApproval, dateApprovalReceived) > 0确保有至少1天的间隔。
• 平均值的精度：用CAST(WorkdayCount AS DECIMAL(10,2))把整数转成小数，避免SQL默认的整数除法导致结果被取整（比如平均2.5天会被当成2天）。

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