asked on 1/4/2013

SQL Server - Help with SQL, Update a field based on current and previous row values

Hi.. I have a table of Meter readings - I need to update a USAGE field based on the before and after reading. Also the twist is - that the Meter Names change so I need to reset and start again when the Meter Name changes. When it starts should be zero

The Data Looks Like This:

NAME READING Date USAGE
Meter A 100 1/1
Meter A 200 1/2
Meter A 500 1/3
Meter B 300 1/1
Meter B 600 1/2

The Result should look like this

NAME READING Date USAGE
Meter A 100 1/1 0
Meter A 200 1/2 200
Meter A 500 1/3 300
Meter B 300 1/1 0
Meter B 600 1/2 300

thx

Microsoft SQL Server Microsoft SQL Server 2005 Microsoft SQL Server 2008

Last Comment

sognoct

1/4/2013

SOLUTION

Ephraim Wangoya

1/4/2013

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

sognoct

1/4/2013

why date does not contain year ? It is not a datetime ?

ASKER CERTIFIED SOLUTION

Scott Pletcher

1/4/2013

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

JElster

1/4/2013

ASKER

Date does have Year - DateTime

sognoct

1/4/2013

UPDATE t1 
SET USAGE = (case when t3.READING is null then 0 else t2.READING - t3.READING end )
from tablename t1 
inner join (select *, ROW_NUMBER()  over (partition by name order by date, name) rn from tablename) t2 on t1.name = t2.name and t1.date = t2.date 
left join (select *, ROW_NUMBER()  over (partition by name order by date, name) rn from tablename) t3 ON t2.name = t3.name AND t2.rn = t3.rn + 1
order by t1.date

Open in new window