基于日期与模型条件的RiskTable风险统计存储过程开发求助

已创建RiskTable表，结构如下：

CREATE TABLE [dbo].[RiskTable]
(
    [ReqID] [int] NOT NULL,
    [ModuleName] [nvarchar](50) NULL,
    [CreatedDate] [datetime2](7) NULL,
    [AssociatedRisk] [nvarchar](50) NULL
)

表中现有数据：

需要开发一个存储过程，满足以下两个核心场景：

• 指定model='saa'、start_date='2023-01-01'、end_date='2023-02-10'时，统计该模型在指定月份（1月、2月）内各风险级别（Low、Medium、High、Critical）的数量，输出需包含Model、Month、Risk、Total no. of risk，缺失的风险级别需显示0。
• 当model为NULL、start_date='2023-04-01'、end_date='2023-05-11'时，统计所有模型在指定月份（4月、5月）内各风险级别的数量，此时Model列显示All，其余格式同上。

以下是满足需求的存储过程，核心思路是生成所有需要的月份+风险级别组合，再与原表数据左连接，确保所有风险级别和月份都能被统计（包括数量为0的情况）：

CREATE PROCEDURE GetRiskStatistics
    @model_name NVARCHAR(50) = NULL,
    @start_date DATETIME2 = NULL,
    @end_date DATETIME2 = NULL
AS
BEGIN
    SET NOCOUNT ON;

    -- 定义所有需要统计的风险级别
    DECLARE @Risks TABLE (Risk NVARCHAR(50));
    INSERT INTO @Risks VALUES ('Low'), ('Medium'), ('High'), ('Critical');

    -- 生成指定日期范围内的所有月份
    DECLARE @Months TABLE (MonthNum INT, MonthName NVARCHAR(3));
    WITH DateRange AS (
        SELECT DATEFROMPARTS(YEAR(@start_date), MONTH(@start_date), 1) AS MonthStart
        UNION ALL
        SELECT DATEADD(MONTH, 1, MonthStart)
        FROM DateRange
        WHERE MonthStart < DATEFROMPARTS(YEAR(@end_date), MONTH(@end_date), 1)
    )
    INSERT INTO @Months
    SELECT MONTH(MonthStart), LEFT(DATENAME(MONTH, MonthStart), 3)
    FROM DateRange;

    -- 统计数据：左连接确保所有风险和月份都被包含
    SELECT
        CASE WHEN @model_name IS NOT NULL THEN @model_name ELSE 'All' END AS Model,
        m.MonthName AS Month, -- 若需数字格式，替换为 RIGHT('0' + CAST(m.MonthNum AS NVARCHAR(2)), 2)
        r.Risk,
        COUNT(rt.ReqID) AS [Total no. of risk]
    FROM @Months m
    CROSS JOIN @Risks r
    LEFT JOIN RiskTable rt
        ON MONTH(rt.CreatedDate) = m.MonthNum
        AND YEAR(rt.CreatedDate) = YEAR(m.MonthStart)
        AND rt.AssociatedRisk = r.Risk
        AND (@model_name IS NULL OR rt.ModuleName = @model_name)
        AND rt.CreatedDate >= @start_date
        AND rt.CreatedDate < @end_date
    GROUP BY
        CASE WHEN @model_name IS NOT NULL THEN @model_name ELSE 'All' END,
        m.MonthName, r.Risk
    ORDER BY
        m.MonthNum,
        CASE r.Risk WHEN 'Low' THEN 1 WHEN 'Medium' THEN 2 WHEN 'High' THEN 3 WHEN 'Critical' THEN 4 END;
END

关键说明 #

1. 全组合覆盖：通过@Risks表变量定义所有风险级别，@MonthsCTE生成指定日期范围内的所有月份，两者交叉连接得到所有需要统计的行，确保无数据的风险级别/月份也能显示0。
2. 动态模型过滤：通过@model_name IS NULL OR rt.ModuleName = @model_name实现模型的可选过滤，当参数为NULL时统计所有模型，Model列显示All。
3. 日期范围准确性：使用CreatedDate >= @start_date AND CreatedDate < @end_date避免包含end_date当天的数据，保证时间范围的严谨性。
4. 排序控制：通过月份数字和风险级别优先级排序，确保输出顺序符合预期。

测试示例 #

场景1：指定Saa模型和1-2月范围 #

EXEC GetRiskStatistics @model_name = 'Saa', @start_date = '2023-01-01', @end_date = '2023-02-10';

输出结果：

场景2：统计所有模型和4-5月范围 #

EXEC GetRiskStatistics @model_name = NULL, @start_date = '2023-04-01', @end_date = '2023-05-11';

输出结果：

如果需要月份显示为数字格式（如01、02），只需将SELECT中的m.MonthName替换为RIGHT('0' + CAST(m.MonthNum AS NVARCHAR(2)), 2)即可。

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