sqlsql-servercalculationaccumulateperiodicity

Calculate periodic values from year to date figures in SQL

I have accumulated year-to-date values for each period. But I need periodic values for each product group / period.

Do you know a solution in SQL Server to achive this in a simple way?

This is my sample data:

Product Group | Period | Amount
-------------------------------
Group 1       |2018/01 | 500
Group 1       |2018/02 | 740
Group 1       |2018/03 | 900
Group 1       |2018/04 | 930

And the result should look like this:

Product Group | Period | Amount
-------------------------------
Group 1       |2018/01 | 500
Group 1       |2018/02 | 240
Group 1       |2018/03 | 160
Group 1       |2018/04 | 30

Thanks for your help! Philipp

Solution

  • You can use LAG function like this:

    SELECT [Product Group], Period,  
       Amount - LAG(Amount, 1,0) OVER (ORDER BY Period) AS Amount
FROM myTable