SQL Server查询：基于前序记录生成数据×系数的新列AmountX

Got it, let's build this query step by step to match your requirements perfectly. Here's how you can generate the result set with all the rules you outlined:

First, we need a way to create a continuous list of monthly dates starting from January 2010. A recursive CTE is a clean, efficient way to do this in SQL Server. Then we'll apply your business logic to calculate Amount and AmountX for each period.

Full Query #

WITH MonthlyDates AS (
    -- Initialize with the first month of 2010
    SELECT 
        CAST('2010-01-01' AS DATE) AS Date
    UNION ALL
    -- Recursively add one month until we hit the desired end year (adjust 2023 to your needs)
    SELECT 
        DATEADD(MONTH, 1, Date)
    FROM MonthlyDates
    WHERE Date < CAST('2023-12-01' AS DATE)
)
SELECT
    -- Format date as 'YYYY-MM' for readability (remove FORMAT to keep as DATE type)
    FORMAT(md.Date, 'yyyy-MM') AS Date,
    -- Replace with your actual Name value (assumed consistent across all records)
    'YourProductName' AS Name,
    -- Amount rule: 0 for 2010, fixed 62.61 from 2011 onward
    CASE 
        WHEN YEAR(md.Date) = 2010 THEN 0.00
        ELSE 62.61 
    END AS Amount,
    -- AmountX rule: 0 for 2010, 63.86 for 2011, then multiply by ~1.02 each subsequent year
    CASE
        WHEN YEAR(md.Date) = 2010 THEN 0.00
        ELSE ROUND(63.86 * POWER(1.02, YEAR(md.Date) - 2011), 2)
    END AS AmountX
FROM MonthlyDates md
ORDER BY md.Date;

Breakdown of Key Parts #

• MonthlyDates CTE: This generates a list of first-day-of-month dates starting from Jan 2010. Adjust the end date (2023-12-01) to extend or shorten the date range as needed.
• Date Column: Formatted as YYYY-MM for clarity, but you can keep it as a native DATE type by removing the FORMAT function if your use case requires it.
• Name Column: Replace YourProductName with your actual name value. If the name varies per record, you'd need to join to a reference table, but the question implies it's consistent.
• Amount Calculation: Simple CASE statement to set 0.00 for all 2010 months, and 62.61 for every month from 2011 onward.
• AmountX Calculation: Uses POWER() to compute the cumulative multiplier. For 2011, the exponent is 0 (so 63.86 * 1 = 63.86). For 2012, exponent is 1 (63.86 * 1.02 = 65.14), 2013 exponent is 2 (65.14 * 1.02 ≈ 66.44), and so on. The ROUND() function ensures we match your example's two-decimal precision.

Notes #

• If your actual multiplier isn't exactly 1.02 (maybe it's a more precise coefficient), just replace 1.02 with the correct value.
• To make the end date dynamic (e.g., up to the current month), replace CAST('2023-12-01' AS DATE) with DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1).

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