Solved

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

Posted on 2013-01-04
5
449 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
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…

751 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