Solved

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

Posted on 2013-01-04
5
448 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:
Scott Pletcher 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

820 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