请求扩展SQL查询：添加复合计算的NPWT活跃中心统计列

核心逻辑 #

• 先提取每个中心的所有有效下单月份，为每个下单记录生成对应的活跃周期（下单月起连续3个自然月：下单月、次月、次次月）
• 生成统计范围内的完整月份序列，避免遗漏任何需要统计的月份
• 关联月份与覆盖该月的活跃中心，去重后得到每个月的复合活跃中心数量

修改后的SQL查询 #

WITH center_order_months AS (
    -- 提取每个中心的有效下单月份（去重，避免同一中心同月多次下单重复计算）
    SELECT 
        DISTINCT CenterCode,
        DATEFROMPARTS(YEAR(DmeOrderDate), MONTH(DmeOrderDate), 1) AS OrderMonth
    FROM [AtHome].[Reporting].[WoundQOrderDetails] w
    LEFT JOIN [AtHome].[Reporting].[DimCenter] c ON c.CenterSK = w.CenterSK
    WHERE 
        DmeOrderDate BETWEEN '2024-01-01' AND GETDATE()
        AND DmeSignedByProvider = 1
        AND OrderType = 'NPWT'
        AND CenterCode IS NOT NULL
),
center_active_periods AS (
    -- 计算每个下单对应的活跃周期：下单月起未来两个月（共3个自然月）
    SELECT 
        CenterCode,
        OrderMonth AS ActiveStart,
        DATEADD(MONTH, 2, OrderMonth) AS ActiveEnd
    FROM center_order_months
),
report_months AS (
    -- 生成2024年至今的所有月份序列
    SELECT 
        DATEFROMPARTS(2024, m.month_num, 1) AS ReportMonth
    FROM (
        VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
    ) m(month_num)
    WHERE DATEFROMPARTS(2024, m.month_num, 1) <= GETDATE()
),
monthly_composite_active AS (
    -- 统计每个月的复合活跃中心数
    SELECT 
        rm.ReportMonth,
        COUNT(DISTINCT cap.CenterCode) AS NPWT_Composite_Active_Centers
    FROM report_months rm
    LEFT JOIN center_active_periods cap 
        ON rm.ReportMonth BETWEEN cap.ActiveStart AND cap.ActiveEnd
    GROUP BY rm.ReportMonth
),
original_monthly_stats AS (
    -- 保留原查询的统计逻辑
    SELECT 
        DATEFROMPARTS(YEAR(DmeOrderDate), MONTH(DmeOrderDate), 1) AS OrderMonth,
        DATENAME(MONTH, DATEADD(MONTH, MONTH(DmeOrderDate), 0) - 1) AS Month,
        COUNT(DISTINCT DmeOrderNumberDisplay) AS NPWT_Order_Volume,  
        COUNT(DISTINCT CenterCode) AS NPWT_Active_Centers
    FROM [AtHome].[Reporting].[WoundQOrderDetails] w
    LEFT JOIN [AtHome].[Reporting].[DimCenter] c ON c.CenterSK = w.CenterSK
    WHERE 
        DmeOrderDate BETWEEN '2024-01-01' AND GETDATE()
        AND DmeSignedByProvider = 1
        AND OrderType = 'NPWT'
        AND CenterCode IS NOT NULL
    GROUP BY 
        DATEFROMPARTS(YEAR(DmeOrderDate), MONTH(DmeOrderDate), 1),
        DATENAME(MONTH, DATEADD(MONTH, MONTH(DmeOrderDate), 0) - 1)
)
-- 合并原统计数据与复合活跃中心数
SELECT 
    oms.Month,
    oms.NPWT_Order_Volume,
    oms.NPWT_Active_Centers,
    COALESCE(mca.NPWT_Composite_Active_Centers, 0) AS NPWT_Composite_Active_Centers
FROM original_monthly_stats oms
LEFT JOIN monthly_composite_active mca ON oms.OrderMonth = mca.ReportMonth
ORDER BY oms.OrderMonth;

关键说明 #

• center_order_months：去重同一中心的同月下单记录，避免重复生成活跃周期
• center_active_periods：自动处理“再次下单重置周期”的需求——同一中心多次下单会生成多个活跃区间，关联时会覆盖重叠时段，确保最新下单的活跃周期生效
• report_months：生成完整月份序列，保证即使某月份无新下单，也能统计到仍处于活跃期的中心
• 最终查询保留原有的所有统计字段，新增NPWT_Composite_Active_Centers列展示复合活跃中心数量

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