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

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

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 !