Search code examples
sqlsql-serverwindow-functions

Running Difference between values of consecutive years

I need to compute a running difference between InterimData values across different years.

Year Month Provider ProviderName InterimData
2022 8 1 A 3866.666667
2022 9 1 A 3866.666667
2022 12 1 A 3866.666667
2023 3 1 A 7416.666667
2023 3 2 B 7416.666667

Constant = 12800

Expected Output:

Year FinalValue Logic
2022 8933.333333 (Constant-Distinct InterimData of 2022 Year)
2023 1516.666666 (FinalValue of 2022 - Distinct interimData of 2023 Year)

I've tried using the LAG function, but it's not working out as expected. I am using sql server 2022.

Can you please help me?

Solution

  • LAG can't help you here because it doesn't carry information from all previous records.

    You could solve this problem by:

    • extracting your unique year-by-year values from your data
    • using a running sum, that stacks up all preceding InterimData values
    • subtracting your aggregated values from the constant.
    SELECT Year_, 
       12800 - SUM(InterimData) OVER(ORDER BY Year_) AS FinalValue
FROM (SELECT DISTINCT Year_, InterimData FROM tab) cte

    Output:

    Year_ FinalValue
    2022 8933.333333
    2023 1516.666666

    Check the demo here.