Solved

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

Posted on 2013-01-04
5
447 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

776 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