T-SQL月度统计函数优化需求：补全零值月份统计

Got it, let's work through this problem together. I know your current function only returns months with counts greater than 1, and you need to adjust it to show every month (even with 0s) between the earliest RELEASED_DATETIME for a specific DEV_ID and today, formatted as MMM-YYYY. Since you already have a calendar table being used in your daily stats function, we can leverage that to make this work smoothly.

Step 1: Lay Out the Core Logic #

The key here is to first generate a complete list of all months in your target date range, then left-join your monthly stats to that list. This ensures we don't skip any months with no activity.

Step 2: The Updated Function Code #

Here's a revised function that meets all your requirements:

CREATE OR ALTER FUNCTION dbo.GetMonthlyEnrollVerifyStats
(
    @DevId INT,
    @MonthRange INT = NULL -- Optional: pass a number to limit to last N months (e.g., 12 for past year)
)
RETURNS TABLE
AS
RETURN
(
    -- First, get the date boundaries for the target DEV_ID
    WITH DateBounds AS (
        SELECT
            -- Get the first day of the month with the earliest RELEASED_DATETIME for this DEV_ID
            COALESCE(DATEFROMPARTS(YEAR(MIN(RELEASED_DATETIME)), MONTH(MIN(RELEASED_DATETIME)), 1), GETDATE()) AS StartMonth,
            -- First day of the current month as the end point
            DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS EndMonth
        FROM SVE_INTERACTION_SESSION_T
        WHERE DEV_ID = @DevId
    ),
    -- Generate every month in the range using your existing calendar table
    AllMonths AS (
        SELECT
            DISTINCT DATEFROMPARTS(YEAR(c.CalendarDate), MONTH(c.CalendarDate), 1) AS MonthStart
        FROM YourCalendarTable c
        CROSS JOIN DateBounds db
        WHERE c.CalendarDate BETWEEN db.StartMonth AND db.EndMonth
            -- Apply month range filter if provided
            AND (@MonthRange IS NULL OR c.CalendarDate >= DATEADD(MONTH, -@MonthRange, GETDATE()))
    ),
    -- Calculate actual enroll/verify counts per month
    MonthlyActivity AS (
        SELECT
            DATEFROMPARTS(YEAR(s.RELEASED_DATETIME), MONTH(s.RELEASED_DATETIME), 1) AS MonthStart,
            COUNT(CASE WHEN s.INTERACTION_TYPE = 'Enroll' THEN 1 END) AS Enrolls,
            COUNT(CASE WHEN s.INTERACTION_TYPE = 'Verify' THEN 1 END) AS Verifies
        FROM SVE_INTERACTION_SESSION_T s
        WHERE s.DEV_ID = @DevId
            AND s.RELEASED_DATETIME >= (SELECT StartMonth FROM DateBounds)
            AND s.RELEASED_DATETIME <= GETDATE()
        GROUP BY DATEFROMPARTS(YEAR(s.RELEASED_DATETIME), MONTH(s.RELEASED_DATETIME), 1)
    )
    -- Combine all months with activity data, fill 0s for missing months
    SELECT
        FORMAT(am.MonthStart, 'MMM-yyyy') AS Month,
        COALESCE(ma.Enrolls, 0) AS Enrolls,
        COALESCE(ma.Verifies, 0) AS Verifies
    FROM AllMonths am
    LEFT JOIN MonthlyActivity ma ON am.MonthStart = ma.MonthStart
    ORDER BY am.MonthStart
);

Key Details to Note: #

• Date Boundaries: We use DATEFROMPARTS to get the first day of the earliest month for your DEV_ID (if there's no data for the ID, it defaults to the current month).
• Calendar Table Usage: The AllMonths CTE pulls distinct months from your calendar table, ensuring we have every month in the range—no gaps.
• Left Join Magic: By joining AllMonths to our activity stats, we preserve all months, and COALESCE turns NULL values (no activity) into 0.
• Formatting: FORMAT(am.MonthStart, 'MMM-yyyy') gives you the Jan-2024 style output. If you're working in a non-English environment, add SET LANGUAGE English; at the top of your query to ensure correct month abbreviations.
• Optional Month Range: The @MonthRange parameter lets you limit results to the last N months (e.g., SELECT * FROM dbo.GetMonthlyEnrollVerifyStats(123, 6) for the past 6 months). Omit it to get all months from the earliest date to today.

Example Usage & Output #

If you run:

SELECT * FROM dbo.GetMonthlyEnrollVerifyStats(123);

You'll get output like this (matching your desired format):

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