月度销售预算增减率计算：全年度适配SQL方案问询

Got it, let's walk through how to fix your SQL to handle all valid months in 2018, including cross-year data for early months and excluding uncalculable late months.

First, let's highlight the issues in your current code:

• Your CTE _sum is defined but never used in the main query, so you're not actually aggregating monthly budgets correctly.
• You're sorting by dtDate which isn't present in your CTE, and you aren't accounting for cross-year data (2017 months needed for 2018's Jan-Mar).
• The logic for "previous 3 months" and "future 3 months" is off: you're calculating an average instead of a sum, and the future value is a single month instead of 3 months total.
• There's no filtering to exclude 2018's Oct-Dec, which can't calculate future 3 months without 2019 data.

Here's the revised SQL that addresses all these points:

-- Step 1: Aggregate monthly budgets across all relevant years (2017 for 2018 early months, 2018 for full year)
WITH MonthlyBudget AS (
    SELECT 
        SalesYear,
        SalesMonth,
        -- Create a numeric year-month value for easy sequential ordering (e.g., 201712, 201801)
        SalesYear * 100 + SalesMonth AS YearMonth,
        SUM(MonthBudget) AS TotalMonthlyBudget
    FROM [SALES].[dbo].[SALES_PLAN]
    WHERE 
        CustomerType NOT IN ('Design')
        -- Include 2017 to cover 2018's Jan-Mar, and 2018 for the target year
        AND SalesYear IN (2017, 2018)
    GROUP BY SalesYear, SalesMonth
),
-- Step 2: Calculate rolling sums for past 3 months and future 3 months
RollingSums AS (
    SELECT 
        SalesYear,
        SalesMonth,
        YearMonth,
        TotalMonthlyBudget,
        -- Sum of the current month + previous 2 months (past 3 consecutive months)
        SUM(TotalMonthlyBudget) OVER (
            ORDER BY YearMonth 
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS Past3MonthsTotal,
        -- Sum of the next 3 months (current +1, +2, +3)
        SUM(TotalMonthlyBudget) OVER (
            ORDER BY YearMonth 
            ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING
        ) AS Future3MonthsTotal
    FROM MonthlyBudget
)
-- Step 3: Filter to only 2018 months, exclude those where future 3 months can't be calculated (Oct-Dec)
SELECT 
    SalesMonth,
    SalesYear,
    TotalMonthlyBudget AS CurrentMonthBudget,
    Past3MonthsTotal,
    Future3MonthsTotal,
    -- Calculate percentage change: ((Future - Past)/Past)*100, handle division by zero
    CASE 
        WHEN Past3MonthsTotal = 0 THEN NULL
        ELSE ROUND(((Future3MonthsTotal - Past3MonthsTotal) * 100.0 / Past3MonthsTotal), 2) 
    END AS [Change%]
FROM RollingSums
WHERE 
    SalesYear = 2018
    -- Only include months where future 3 months exist (Jan-Sep 2018)
    AND SalesMonth <= 9
ORDER BY SalesMonth;

Key improvements explained: #

• Cross-year support: By including 2017 data in the initial aggregation, we can calculate the past 3 months for 2018's Jan-Mar (e.g., Jan 2018 uses Nov 2017, Dec 2017, Jan 2018).
• Sequential ordering: Using SalesYear * 100 + SalesMonth creates a continuous numeric value that works across years, so the window functions correctly pick up the right months regardless of year boundaries.
• Correct rolling sums: The ROWS BETWEEN clauses ensure we're summing exactly 3 consecutive months for both past and future periods.
• Valid month filtering: We exclude 2018's Oct-Dec since there's no 2019 data to compute the future 3 months.
• Division safety: The CASE statement handles cases where past 3 months sum to zero to avoid errors, and rounds the percentage to 2 decimal places for readability.

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