带累计的条件查询：关于给定SQL存储过程的技术咨询

Hey there! Let's walk through how to polish that monthly sum query you started, add the cumulative functionality you need, and wrap it into a maintainable stored procedure. Your initial approach to pivoting monthly amounts is solid, but we can make it cleaner, more flexible, and easier to update long-term.

First, let's recap your existing code (to set the context):

SELECT 
    employeename,
    ISNULL(SUM(CASE WHEN DATEPART(mm, cs.scheduledate) = 1 THEN cs.amount ELSE 0 END), 0) AS january,
    ISNULL(SUM(CASE WHEN DATEPART(mm, cs.scheduledate) = 2 THEN cs.amount ELSE 0 END), 0) AS february,
    ISNULL(SUM(CASE WHEN DATEPART(mm, cs.scheduledate) = 3 THEN cs.amount ELSE 0 END), 0) AS march,
    ISNULL(SUM(CASE WHEN DATEPART(mm, cs.scheduledate) = 4 THEN cs.amount ELSE 0 END), 0) AS april,
    ISNULL(SUM(CASE WHEN DATEPART(mm, cs.scheduledate) = 5 THEN cs.amount ELSE 0 END), 0) AS may
    -- ... rest of the months
FROM 
    -- Your table/join logic here
GROUP BY 
    employeename

Key Pain Points to Fix #

• Repetitive CASE Statements: Writing 12 almost-identical lines is tedious and error-prone (especially if you need to add filters like year later).
• Missing Cumulative Totals: Your current code only gets monthly sums, not running totals (e.g., total from Jan to Feb, Jan to March, etc.).
• Hardcoded Months: If you ever need to adjust the date range (e.g., focus on Q1 only), you'll have to rewrite half the query.

Solution 1: Static Pivot with Cumulative Totals (For Fixed Month Ranges) #

If you know you'll always need all 12 months, this clean, readable version uses CTEs and window functions to calculate cumulative sums without messy subqueries:

WITH MonthlySums AS (
    -- First, calculate raw monthly sums per employee
    SELECT 
        employeename,
        DATEPART(mm, cs.scheduledate) AS month_num,
        ISNULL(SUM(cs.amount), 0) AS monthly_amount
    FROM 
        -- Replace with your actual table joins (e.g., Employees e JOIN ClientSchedules cs ON e.EmployeeID = cs.EmployeeID)
    WHERE 
        YEAR(cs.scheduledate) = 2024 -- Add year filter here
    GROUP BY 
        employeename, DATEPART(mm, cs.scheduledate)
),
CumulativeSums AS (
    -- Calculate running totals using window functions
    SELECT 
        employeename,
        month_num,
        monthly_amount,
        SUM(monthly_amount) OVER (PARTITION BY employeename ORDER BY month_num) AS cumulative_amount
    FROM MonthlySums
)
-- Pivot the results into column-based months
SELECT 
    employeename,
    ISNULL(MAX(CASE WHEN month_num = 1 THEN monthly_amount END), 0) AS january,
    ISNULL(MAX(CASE WHEN month_num = 1 THEN cumulative_amount END), 0) AS january_cumulative,
    ISNULL(MAX(CASE WHEN month_num = 2 THEN monthly_amount END), 0) AS february,
    ISNULL(MAX(CASE WHEN month_num = 2 THEN cumulative_amount END), 0) AS february_cumulative,
    -- Repeat this pattern for March through December
    ISNULL(MAX(CASE WHEN month_num = 12 THEN monthly_amount END), 0) AS december,
    ISNULL(MAX(CASE WHEN month_num = 12 THEN cumulative_amount END), 0) AS december_cumulative
FROM CumulativeSums
GROUP BY employeename
ORDER BY employeename;

Solution 2: Dynamic SQL Stored Procedure (For Flexible Ranges) #

If you need to adjust the month range or make the procedure reusable across years, dynamic SQL is the way to go. This generates the month columns automatically, so you never have to rewrite the pivot logic:

CREATE PROCEDURE GetEmployeeMonthlyCumulativeSums
    @Year INT = NULL -- Optional: Pass a year, or use current year by default
AS
BEGIN
    SET NOCOUNT ON;

    -- Set default year if none is provided
    IF @Year IS NULL
        SET @Year = YEAR(GETDATE());

    DECLARE @PivotColumns NVARCHAR(MAX);
    DECLARE @DynamicSQL NVARCHAR(MAX);

    -- Generate month column definitions (monthly sum + cumulative total)
    WITH Months AS (
        SELECT 1 AS month_num, 'january' AS month_name
        UNION ALL SELECT 2, 'february'
        UNION ALL SELECT 3, 'march'
        UNION ALL SELECT 4, 'april'
        UNION ALL SELECT 5, 'may'
        UNION ALL SELECT 6, 'june'
        UNION ALL SELECT 7, 'july'
        UNION ALL SELECT 8, 'august'
        UNION ALL SELECT 9, 'september'
        UNION ALL SELECT 10, 'october'
        UNION ALL SELECT 11, 'november'
        UNION ALL SELECT 12, 'december'
    )
    SELECT @PivotColumns = STRING_AGG(
        CONCAT(
            'ISNULL(MAX(CASE WHEN month_num = ', month_num, ' THEN monthly_amount END), 0) AS ', month_name, ',',
            'ISNULL(MAX(CASE WHEN month_num = ', month_num, ' THEN cumulative_amount END), 0) AS ', month_name, '_cumulative'
        ),
        ','
    )
    FROM Months;

    -- Build the full dynamic query
    SET @DynamicSQL = CONCAT('
        WITH MonthlySums AS (
            SELECT 
                employeename,
                DATEPART(mm, cs.scheduledate) AS month_num,
                ISNULL(SUM(cs.amount), 0) AS monthly_amount
            FROM 
                -- Replace with your actual table/join logic
                Employees e INNER JOIN ClientSchedules cs ON e.EmployeeID = cs.EmployeeID
            WHERE 
                YEAR(cs.scheduledate) = ', @Year, '
            GROUP BY 
                employeename, DATEPART(mm, cs.scheduledate)
        ),
        CumulativeSums AS (
            SELECT 
                employeename,
                month_num,
                monthly_amount,
                SUM(monthly_amount) OVER (PARTITION BY employeename ORDER BY month_num) AS cumulative_amount
            FROM MonthlySums
        )
        SELECT 
            employeename,
            ', @PivotColumns, '
        FROM CumulativeSums
        GROUP BY employeename
        ORDER BY employeename;
    ');

    -- Execute the dynamic query
    EXEC sp_executesql @DynamicSQL;
END;

Pro Tips for Optimization & Maintainability #

• Parameterize Everything: The @Year parameter makes the procedure reusable and prevents SQL injection.
• Index Strategically: Add indexes on columns used in filtering, grouping, and joining to speed up queries:

  CREATE NONCLUSTERED INDEX IX_ClientSchedules_EmployeeID_ScheduleDate ON ClientSchedules (EmployeeID, ScheduleDate) INCLUDE (Amount);
  

• Handle NULLs: Using ISNULL() ensures you get 0 instead of NULL for months with no activity, which makes reporting cleaner.
• Test Edge Cases: Verify results for employees with no activity in some months, or for years with partial data (e.g., mid-year).

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