CODEWITHSUNDEEP
sqlsql-servercalculationaccumulateperiodicity
Philipp
Philipp

Reputation: 23

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

Upvotes: 1

Views: 980

Answers (6)

G Aravind Reddy
G Aravind Reddy

Reputation: 1

A small change in query

SELECT [ProductGroup], Period,  
      Amount- LAG(Amount, 1,0) OVER (ORDER BY Period)   AS Amount
FROM tablename

Upvotes: 0

etsa
etsa

Reputation: 5060

Just assuming you can't use LAG (eg. MSSQL 2008) and that you can have several records for each ProductGroup and Period, you can use following query.

Sample table and values:

CREATE TABLE GR (Product_Group VARCHAR(10), Period VARCHAR(6), Amount INT);

INSERT INTO GR VALUES ('Group 1',  '201801', 500)
, ('Group 1',  '201802', 740)
,('Group 1',  '201803', 900)
,('Group 1',  '201804', 930)
;
INSERT INTO GR VALUES ('Group 2',  '201801', 500)
, ('Group 2',  '201803', 800)
,('Group 2',  '201803', 1000)
,('Group 2',  '201804', 1200)
;

Query uses GROUP BY (and CTE to simplify reading) to group for ProductGroup and Period, and RowNumber to seek previous value (if you have fixed period, ie if you want to show a record for each month when month value is missing you can use a Tally Date table)

WITH X AS (SELECT  Product_Group, Period, SUM(Amount) AS Amount_TOT
            , ROW_NUMBER() OVER (PARTITION BY Product_Group ORDER BY PERIOD) AS RN
            FROM GR GROUP BY Product_group, Period)  
SELECT Product_Group, Period, Amount_TOT,  Amount_TOT_PREC, Amount_TOT-ISNULL(Amount_TOT_PREC,0) AS Delta 
FROM (SELECT  A.Product_Group, A.Period, A.Amount_TOT, B.Amount_TOT AS Amount_TOT_PREC       
        FROM X A
        LEFT JOIN X B ON A.Product_Group=B.Product_Group AND A.RN-1 = B.RN
        ) C

Output

+---------------+--------+------------+-----------------+-------+
| Product_Group | Period | Amount_TOT | Amount_TOT_PREC | Delta |
+---------------+--------+------------+-----------------+-------+
| Group 1       | 201801 |        500 | NULL            |   500 |
| Group 1       | 201802 |        740 | 500             |   240 |
| Group 1       | 201803 |        900 | 740             |   160 |
| Group 1       | 201804 |        930 | 900             |    30 |
| Group 2       | 201801 |        500 | NULL            |   500 |
| Group 2       | 201803 |       1800 | 500             |  1300 |
| Group 2       | 201804 |       1200 | 1800            |  -600 |
+---------------+--------+------------+-----------------+-------+

Using LAG (this function return a value of a previous record, pls check Microsoft Documentation) is faster and more readable:

WITH X AS (SELECT Product_Group, Period
                  , SUM(Amount) AS Amount_TOT           
            FROM GR GROUP BY Product_group, Period)  
SELECT Product_Group, Period, Amount_TOT, AMOUNT_PREC
       , Amount_TOT-ISNULL(AMOUNT_PREC,0) AS Delta 
FROM (SELECT  Product_Group, Period, Amount_TOT 
              , LAG(Amount_TOT) OVER (PARTITION BY Product_Group 
                 ORDER BY PERIOD) AS AMOUNT_PREC     
        FROM X) A;

Same output as above

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I would use lag() function :

select [Product Group], Period,  
       Amount - LAG(Amount, 1,0) OVER (ORDER BY Period) AS Amount
from table t'

If you have older version of SQL, then you can also use apply :

select t.product, t.period, coalesce(t.amount-t1.amount, t.amount) as amount
from table t outer apply
     ( select top (1) t1.*
       from table t1
       where t1.product = t.product and 
             t1.period < t.period
             order by t1.period desc
     ) t1;

Upvotes: 0

Enkh-Erdene
Enkh-Erdene

Reputation: 71

I hope it will be help you.

WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY t.[Product Group],t.[Period]),
t.*
FROM YourTbl AS t
)

SELECT CTE.[Product Group],CTE.Period,-1*(ISNULL(prev.Amount,0)-CTE.Amount) AS Amount 
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1

Upvotes: 0

apomene
apomene

Reputation: 14389

You can use LAG function like this:

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

Upvotes: 0

Nitzan
Nitzan

Reputation: 1

Hope this helps:

Select 
ProductGroup, 
Period, 
Amount = Amount-ISNULL((Select Amount from TempTable where Period = (select max(Period) from TempTable where Period < Main.Period)),0)
From TempTable Main
Order by Period

I assumed Period is a datetime type (2018-01-01)

Upvotes: 0

Related Questions