CODEWITHSUNDEEP
sql-server-2008
Shamim
Shamim

Reputation: 703

How to calculate the running difference of the same column in a table with SQL query?

I have the following table structure with data as

ReadingDate   Unit
01-05-2011    10 
01-06-2011    20 
01-07-2011    40 
01-08-2011    40

AND I want to the following result with T-Sql query. I am using sql server 2008 R2

  ReadingDate   Unit   UnitConsumed 
    01-05-2011    10      10
    01-06-2011    20      10
    01-07-2011    40      20
    01-08-2011    40      0

Upvotes: 3

Views: 5735

Answers (2)

Jens Wagemaker
Jens Wagemaker

Reputation: 374

This can be done in SQL Server using the LAG function. See LAG function.

Upvotes: 4

Hasan Fahim
Hasan Fahim

Reputation: 3885

You could try the following:

With tblDifference as
(
    Select Row_Number() OVER (Order by ReadingDate) as RowNumber,ReadingDate,Unit from  TestTable
)

Select Cur.ReadingDate, Cur.Unit, ISNULL((Cur.Unit-Prv.Unit),Cur.Unit) as UnitConsumed
from tblDifference Cur 
Left Outer Join tblDifference Prv 
On Cur.RowNumber=Prv.RowNumber+1

Plain and simple !

Upvotes: 1

Related Questions