编写存储过程统计指定年度每周末未结案理赔单数及相关数据

Got it, let's build the stored procedure that delivers the weekly claim metrics you need. This procedure will take a target year as input and return each week's number, the Sunday (weekend date), count of claims received that week, count of claims closed that week, and the number of open claims at the end of the week.

Here's the complete stored procedure:

CREATE PROCEDURE GetWeeklyClaimMetrics
    @TargetYear INT
AS
BEGIN
    SET NOCOUNT ON;
    -- Set week start to Monday so each week ends on Sunday
    SET DATEFIRST 1;

    -- CTE to generate every Sunday in the target year (covers all weeks)
    WITH YearlyWeeks AS (
        SELECT 
            -- Calculate the first Sunday of the target year
            DATEADD(week, DATEDIFF(week, 0, DATEADD(year, @TargetYear-1900, 0)) + 1, -1) AS WeekEndDate
        UNION ALL
        SELECT 
            DATEADD(week, 1, WeekEndDate)
        FROM YearlyWeeks
        WHERE YEAR(DATEADD(week, 1, WeekEndDate)) = @TargetYear
    )
    SELECT
        DATEPART(week, yw.WeekEndDate) AS WeekNumber,
        yw.WeekEndDate AS WeekendDate,
        -- Count of claims received during the week (Mon-Sun)
        COALESCE(rc.ReceivedCount, 0) AS WeeklyReceivedClaims,
        -- Count of claims closed during the week
        COALESCE(cc.ClosedCount, 0) AS WeeklyClosedClaims,
        -- Open claims at week's end: all received on/before Sunday, not closed or closed after Sunday
        (SELECT COUNT(*) 
         FROM claims c
         WHERE c.Reception_Date <= yw.WeekEndDate
           AND (c.Fixing_Date IS NULL OR c.Fixing_Date > yw.WeekEndDate)) AS OpenClaimsAtWeekend
    FROM YearlyWeeks yw
    LEFT JOIN (
        -- Aggregate received claims per week
        SELECT 
            DATEPART(week, c.Reception_Date) AS WeekNum,
            COUNT(*) AS ReceivedCount
        FROM claims c
        WHERE YEAR(c.Reception_Date) = @TargetYear
        GROUP BY DATEPART(week, c.Reception_Date)
    ) rc ON DATEPART(week, yw.WeekEndDate) = rc.WeekNum
    LEFT JOIN (
        -- Aggregate closed claims per week
        SELECT 
            DATEPART(week, c.Fixing_Date) AS WeekNum,
            COUNT(*) AS ClosedCount
        FROM claims c
        WHERE YEAR(c.Fixing_Date) = @TargetYear
          AND c.Fixing_Date IS NOT NULL
        GROUP BY DATEPART(week, c.Fixing_Date)
    ) cc ON DATEPART(week, yw.WeekEndDate) = cc.WeekNum
    ORDER BY yw.WeekEndDate;
END

Breakdown of Key Parts: #

• SET DATEFIRST 1: This configures SQL Server to treat Monday as the first day of the week, so each week runs from Monday to Sunday—matching your requirement of Sunday as the weekend date.
• YearlyWeeks CTE: This generates a list of all Sunday dates for the target year. Even if a week has no claims, this ensures we still have a row for it in the output.
• Weekly Received Claims: We group claims by the week their Reception_Date falls into, counting how many were received that week. COALESCE ensures we show 0 instead of NULL for weeks with no received claims.
• Weekly Closed Claims: Similarly, we count claims where Fixing_Date is non-NULL (meaning closed) and falls within the current week.
• Open Claims at Weekend: This calculates the cumulative number of claims that were received on or before the week's Sunday, and either haven't been closed yet (Fixing_Date IS NULL) or were closed after the weekend (Fixing_Date > WeekEndDate).

Testing the Procedure #

To use this with your sample data for 2020, just run:

EXEC GetWeeklyClaimMetrics @TargetYear = 2020;

内容的提问来源于stack exchange，提问作者Kemal AL GAZZAH