CODEWITHSUNDEEP
t-sqlsql-server-2012
Looking_for_answers
Looking_for_answers

Reputation: 343

How to find the cumulative difference between two rows and display it accordingly?

Thank you in advance.

I have a table: 

ID | NAME    | Value
---+---------+------------- 
 1 | ABC     | 1987698.987
 2 | DEF     | 1987870.987
 3 | GHI     | 1987990.987
 4 | JKL     | 1988200.987

Output: 

ID | NAME  | Value       | Cumulative_difference
---+-------+-------------+----------------------
 1 | ABC   | 1987698.987 |      0  
 2 | DEF   | 1987870.987 |    172
 3 | GHI   | 1987990.987 |    120
 4 | JKL   | 1988200.987 |    210

The calculation for cumulative_difference is = (1987870.987 - 1987698.987 ) else '0'

This is the query I have used but I am getting negative values:

 select 
     id, name, value, value 
     -LAG(value, 1, 1) over (partition by name order by id)
 from 
     table1

Any help would be appreciated.

I am using SQL Server 2012.

Thank you

Upvotes: 0

Views: 3280

Answers (3)

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

try the below script, using LAG (Transact-SQL)

select id,name,value,isnull(value - LAG(value) over(order by id),0) Cumulative_difference
from table1

Out put:

id  name  value         Cumulative_difference
1   ABC   1987698.987   0.00
2   DEF   1987870.987   172.00
3   GHI   1987990.987   120.00
4   JKL   1988200.987   210.00

Upvotes: 2

zarruq
zarruq

Reputation: 2465

You can use standard ANSI OLAP functions or LAG() along with coalesce as below.

SELECT t1.*
    ,coalesce(value - min(value) OVER (
            ORDER BY id rows BETWEEN 1 preceding
                    AND 1 preceding
            ), 0) AS commulative_diff
FROM table1 t1

OR

SELECT t1.*,
       coalesce(value - lag(value) over(
                                        ORDER BY id),0) AS commulative_diff
FROM table1 t1

Result

ID  NAME    Value        commulative_diff
------------------------------------
1   ABC     1987698,987  0.000
2   DEF     1987870,987  172.000
3   GHI     1987990,987  120.000
4   JKL     1988200,987  210.000

DEMO

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

What about just normal joins (if you have auto increment ids) ?

SELECT T.ID,
       T.NAME,
       T.VALUE,
       ISNULL(T.VALUE - T1.VALUE, 0) [Cumulative_difference]
FROM table T
     LEFT JOIN table T1 ON T1.ID + 1 = T.ID;

Result :

ID  NAME    VALUE       Cumulative_difference
1   ABC     1987698.987 0.00
2   DEF     1987870.987 172.00
3   GHI     1987990.987 120.00
4   JKL     1988200.987 210.00

Upvotes: 1

Related Questions