CODEWITHSUNDEEP
sqlsql-servermath
Masood Virk
Masood Virk

Reputation: 33

Need Query to Calculate Data from Previous in SQL Server

I have below sample data table in SQL Server

enter image description here

Below is my expected result to calculate results in Consumption column

enter image description here

Upvotes: 0

Views: 69

Answers (3)

Ben Thul
Ben Thul

Reputation: 32697

SQL 2012+:

CREATE TABLE #foobar (item INT, d DATE, reading INT);

INSERT INTO #foobar
        ( item, d, reading )
VALUES  
    (1, '2015-06-01', 10),
    (2, '2015-06-02', 20),
    (3, '2015-06-03', 25);

SELECT *, COALESCE(reading - LAG(reading) OVER (ORDER BY d), 0)
FROM #foobar

Upvotes: 0

Jeremy C.
Jeremy C.

Reputation: 2465

I wrote this in mysql and it works, if you need it for a different dbms let me know: fiddle

select t1.item, t1.mydate, t1.reading, 
       coalesce((t1.reading - t2.reading),0) as consumption
from mytable t1 left join
mytable t2
on t2.item = (select max(item) from
                 mytable where item < t1.item)
order by t1.item asc

Note: this code will also work if your item codes do not increase by 1 every time (example you have item 1, 5, 7 ,8 it will calculate difference between 5 -1, 7 - 5 and 8-7

EDIT: code also works on sql-server

Upvotes: 0

Tirma
Tirma

Reputation: 674

the query you need uses a join with the same table:

select A.Item, A.Date, A.reading, A.Reading - isnull(B.reading,0) as Consumption
from TABLENAME A
left join TABLENAME B on A.ITEM = B.Item+1

Upvotes: 1

Related Questions