Solved

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

Posted on 2013-01-04
5
444 Views
Last Modified: 2013-01-04
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
0
Comment
Question by:JElster
5 Comments
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 250 total points
ID: 38744430
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
 
LVL 9

Expert Comment

by:sognoct
ID: 38744433
why date does not contain year ? It is not a datetime ?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 38744435
;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
 
LVL 1

Author Comment

by:JElster
ID: 38744446
Date does have Year - DateTime
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38744527
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now