Hendrik Kleine - 1 year ago 65
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!

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download