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
LVL 1
JElsterAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
;WITH CTE_READINGS AS (
      SELECT
            NAME, READING, Date,
            ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY DATE) AS row_num
      FROM dbo.tablename
)
SELECT
    r2.NAME, r2.READING, r2.Date, ISNULL(r2.READING - r1.READING, 0) AS USAGE
FROM CTE_READINGS r2
LEFT OUTER JOIN CTE_READINGS r1 ON
    r1.NAME = r2.NAME AND
    r1.row_num = r2.row_num - 1
0
 
Ephraim WangoyaConnect With a Mentor Commented:
Use a cte as follows

;with cte as
(
	select *,
		ROW_NUMBER() over (partition by meter order by readdate, meter) rn
	from YourTable
)

select meter, reading, readdate,0 [Usage]
from cte
where rn = 1
union
select A.meter, a.reading, a.readdate, b.reading - a.reading [Usage]
from cte a
inner join cte b on (a.meter = b.meter) and (b.rn = a.rn +1)

Open in new window

0
 
sognoctCommented:
why date does not contain year ? It is not a datetime ?
0
 
JElsterAuthor Commented:
Date does have Year - DateTime
0
 
sognoctCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.