CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
B.Patel
B.Patel

Reputation: 31

find a difference between current value and previous value in sql

I need to find a difference between current value and previous value

Here is an example:

enter image description here

Here is required Output:

enter image description here

Explanation of output: We need to have a difference between current value and previous value and need to find value_diff.

E.g. We have 500 in 1Q16 and 2Q16 so, Difference between both of them is 0..So, It needs to display 0 in value_diff column in 2Q16 rows.

We have 1000 in 3Q16 so, Differnce between 3Q16 and 2Q16 is 500.. So, it needs to display 500 in value_diff column in 3Q16 row.

I tried some coding using CTE but I did not find a perfect way to do this one..

Any advice would be helpful..

Upvotes: 0

Views: 952

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

SQL Server 2012+ supports lag() which does this directly. In SQL Server 2008, I would recommend a outer apply. Let me first assume that quarter is a reasonable format that orders correctly, such as YYYY-Q:

select t.*, (t.value - tprev.value) as diff
from t outer apply
     (select top 1 tprev.*
      from t tprev
      where tprev.id = t.id and
            tprev.yyyyq < t.yyyyq
      order by tprev.yyyyq desc
     ) tprev;

You have a lousy format for quarter. Let's fix that with a CTE:

with t as (
      select e.*,
             ('20' + right(quarter, 2)) + '-' + left(quarter, 1)) as yyyyq
      from example e
     ) 
select t.*, (t.value - tprev.value) as diff
from t outer apply
     (select top 1 tprev.*
      from t tprev
      where tprev.id = t.id and
            tprev.yyyyq < t.yyyyq
      order by tprev.yyyyq desc
     ) tprev;

Upvotes: 2

Related Questions