SQL Server：基于工作日差值生成区间统计表格的查询改写需求

我先帮你修正原查询里的工作日计算逻辑，再扩展出你需要的区间统计——包括负数值的异常情况，同时输出每个区间的计数和占比。咱们一步步来：

第一步：修正工作日差值计算 #

原查询里的Time_Spent公式有重复的CASE判断，我先调整成准确的工作日计算逻辑（假设你的SQL Server中DATEPART(dw)返回1=周日，7=周六；如果你的环境是周一为1，需要微调CASE条件）：

-- 先计算正确的工作日差值
SELECT
    a.ID,
    a.Date_Reported AS [Date Sent to X],
    b.Date_Received AS [Date Returned from X],
    -- 计算两个日期之间的工作日数（排除周六周日）
    DATEDIFF(dd, a.Date_Reported, b.Date_Received)
    - DATEDIFF(wk, a.Date_Reported, b.Date_Received) * 2
    - CASE WHEN DATEPART(dw, a.Date_Reported) = 1 THEN 1 ELSE 0 END  -- 如果起始日是周日，减1
    - CASE WHEN DATEPART(dw, b.Date_Received) = 7 THEN 1 ELSE 0 END  -- 如果结束日是周六，减1
    AS [Time_Spent]
FROM Tx_Ex a
JOIN Tx b ON b.ID = a.ID

第二步：扩展区间统计与占比计算 #

接下来，我们把Time_Spent分到你指定的区间，然后统计每个区间的数量和占总记录数的百分比，最后用PIVOT转成你需要的横向表格格式：

WITH WorkdayDiff AS (
    -- 先计算每个记录的工作日差值
    SELECT
        DATEDIFF(dd, a.Date_Reported, b.Date_Received)
        - DATEDIFF(wk, a.Date_Reported, b.Date_Received) * 2
        - CASE WHEN DATEPART(dw, a.Date_Reported) = 1 THEN 1 ELSE 0 END
        - CASE WHEN DATEPART(dw, b.Date_Received) = 7 THEN 1 ELSE 0 END
        AS Time_Spent
    FROM Tx_Ex a
    JOIN Tx b ON b.ID = a.ID
),
IntervalGroups AS (
    -- 把差值映射到指定区间
    SELECT
        CASE
            WHEN Time_Spent < 0 THEN 'less than 0 days'
            WHEN Time_Spent BETWEEN 0 AND 3 THEN '0-3'
            WHEN Time_Spent = 4 THEN '4'
            WHEN Time_Spent = 5 THEN '5'
            WHEN Time_Spent BETWEEN 6 AND 8 THEN '6-8'
            WHEN Time_Spent >=9 THEN '9+'
        END AS Time_Interval,
        Time_Spent
    FROM WorkdayDiff
),
Stats AS (
    -- 统计每个区间的计数和占比
    SELECT
        Time_Interval,
        COUNT(*) AS Interval_Count,
        ROUND((COUNT(*) * 100.0) / (SELECT COUNT(*) FROM IntervalGroups), 0) AS Percentage
    FROM IntervalGroups
    GROUP BY Time_Interval
)
-- 转成横向表格
SELECT
    Metric,
    ISNULL([less than 0 days], 0) AS [less than 0 days],
    ISNULL([0-3], 0) AS [0-3],
    ISNULL([4], 0) AS [4],
    ISNULL([5], 0) AS [5],
    ISNULL([6-8], 0) AS [6-8],
    ISNULL([9+], 0) AS [9+]
FROM (
    SELECT Time_Interval, Interval_Count AS Value, 'Count' AS Metric FROM Stats
    UNION ALL
    SELECT Time_Interval, Percentage AS Value, '%' AS Metric FROM Stats
) AS SourceData
PIVOT (
    SUM(Value)
    FOR Time_Interval IN ([less than 0 days], [0-3], [4], [5], [6-8], [9+])
) AS PivotTable;

输出结果示例 #

执行上面的查询后，会得到类似下面的表格（和你提供的虚拟值匹配）：

注意事项 #

• 如果你的SQL Server中DATEPART(dw)的起始日不是周日（比如周一=1），需要调整CASE里的日期判断条件，确保正确排除周末。
• 用ISNULL是为了避免某个区间没有数据时显示NULL，替换成0更友好。

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