Hendrik Kleine Hendrik Kleine - 2 months ago 6
SQL Question

SQL - Value difference between specific rows

My query is as follows

SELECT
LEFT(TimePeriod,6) Period, -- string field with YYYYMMDD
SUM(Value) Value
FROM f_Trans_GL
WHERE Account = 228
GROUP BY TimePeriod


And it returns

Period Value
201412 80
201501 20
201502 30
201506 50
201509 100
201509 100


I'd like to know the Value difference between rows where the period is 1 month apart. The calculation being [value period] - [value period-1].

The desired output being;

Period Value Calculated
201412 80 80 - null = 80
201501 20 20 - 80 = -60
201502 30 30 - 20 = 10
201506 50 50 - null = 50
201509 100 (100 + 100) - null = 200


This illustrates a second challenge, as the period needs to be evaluated if the year changes (the difference between 201501 and 201412 is one month).

And the third challenge being a duplicate Period (201509), in which case the sum of that period needs to be evaluated.

Any indicators on where to begin, if this is possible, would be great!

Thanks in advance

Answer

As pointed by other, first mistake is in Group by you need to Left(timeperiod, 6) instead of timeperiod.

For remaining calculation try something like this

;WITH cte 
     AS (SELECT LEFT(timeperiod, 6)                      Period, 
                Sum(value)                               Value, 
                Cast(LEFT(timeperiod, 6) + '01' AS DATE) ord_date 
         FROM   f_trans_gl 
         WHERE  account = 228 
         GROUP  BY LEFT(timeperiod, 6)) 
SELECT a.period, 
       a.value, 
       a.value - Isnull(b.value, 0) 
FROM   cte a 
       LEFT JOIN cte b 
              ON a.ord_date = Dateadd(month, 1, b.ord_date) 

If you are using SQL SERVER 2012 then this can be easily done using LAG analytic function

Comments