如何修改SQL报表计算指定日期范围不同病例数的平均天数？

核心思路 #

要实现需求，需要分两步处理：

1. 先按日期统计每日总病例数，彻底移除病理医生维度；
2. 基于每日病例数的统计结果，计算各病例数（6/7/8/9/10）对应的天数，以及这些天数在日期范围内的占比（即你要的“平均天数”）。

修改后的SQL代码 #

DECLARE 
    @StartDate datetime = '2021-07-01', 
    @EndDate datetime = '2022-06-30'; -- 修正原代码错误：6月没有31日

-- 第一步：按日期统计每日总病例数，移除病理医生维度
WITH DailyCaseCounts AS (
    SELECT 
        CAST(a.created_date AS date) AS Created_Date,
        COUNT(*) AS Daily_Cases
    FROM accession_2 a
    WHERE a.created_date >= @StartDate 
      AND a.created_date <= @EndDate
    GROUP BY CAST(a.created_date AS date)
)
-- 第二步：计算各病例数对应的天数及平均占比
SELECT
    COUNT(*) AS Total_Days_With_Cases, -- 日期范围内有病例的总天数
    -- 病例数=6的天数及占比
    SUM(CASE WHEN Daily_Cases = 6 THEN 1 ELSE 0 END) AS Days_With_6_Cases,
    CAST(SUM(CASE WHEN Daily_Cases = 6 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) AS Avg_Ratio_6_Cases,
    -- 病例数=7的天数及占比
    SUM(CASE WHEN Daily_Cases = 7 THEN 1 ELSE 0 END) AS Days_With_7_Cases,
    CAST(SUM(CASE WHEN Daily_Cases = 7 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) AS Avg_Ratio_7_Cases,
    -- 病例数=8的天数及占比
    SUM(CASE WHEN Daily_Cases = 8 THEN 1 ELSE 0 END) AS Days_With_8_Cases,
    CAST(SUM(CASE WHEN Daily_Cases = 8 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) AS Avg_Ratio_8_Cases,
    -- 病例数=9的天数及占比
    SUM(CASE WHEN Daily_Cases = 9 THEN 1 ELSE 0 END) AS Days_With_9_Cases,
    CAST(SUM(CASE WHEN Daily_Cases = 9 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) AS Avg_Ratio_9_Cases,
    -- 病例数=10的天数及占比
    SUM(CASE WHEN Daily_Cases = 10 THEN 1 ELSE 0 END) AS Days_With_10_Cases,
    CAST(SUM(CASE WHEN Daily_Cases = 10 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) AS Avg_Ratio_10_Cases
FROM DailyCaseCounts;

关键修改说明 #

• 移除病理医生维度：通过CTE DailyCaseCounts 只按日期分组统计，彻底剥离病理医生的影响；
• 统计各病例数天数：用SUM(CASE...)语法，对每日病例数等于目标值的天数做计数；
• 计算平均占比：将目标天数除以总病例天数，得到该病例数在日期范围内的平均出现比例；
• 修正日期错误：原代码中@EndDate写为06/31/2022（6月无31日），改为标准ISO格式的2022-06-30避免日期解析错误。

如果你的“平均天数”仅指单纯的天数统计（而非占比），可以只保留Days_With_N_Cases系列列即可。

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