Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

UPDATE Row using values from prior row

Posted on 2008-09-30
9
Medium Priority
?
752 Views
Last Modified: 2012-05-05
Hello,
I have this:

UPDATE MONEY SET Balance = Credit - Debit + "X"

Ok, that "X" is supposed to be the prior row. I need to calculate the Balance, which is the result of the previous balance + the result of credit + debit. The thing is by the nature of our business we need to alter some old rows and recalculate the balance. The update works that way? How to get the value for a column in the previous row?

thanks.
0
Comment
Question by:starusa
[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
  • 4
  • 3
9 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 22610200
Do you have an incrementing primary key (PK) or some other column that indicates a row is older than the one you are on?  If so, you could do something like this:


UPDATE MONEY 
SET Balance = Credit - Debit + 
IsNull((SELECT b.balance FROM MONEY b WHERE b.pkColumn IN (SELECT max(pkColumn) FROM MONEY c WHERE c.pkColumn < MONEY.pkColumn)), 0.00)

Open in new window

0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 1000 total points
ID: 22610767
I would POSSIBLY suggest not storing the balance then, if values are subject to change.  But that can be figured out

Or, you will have to run this frequently:

;with CurrentPast as
(select AccountNo, TransDate, Credit, Debit, cast(Null as money) as PreviousCredit, cast(Null as money) as PreviousDebit, row_number() over (partition by AccountNo order by TransDate) rn
from Money
union all
select mpAccountNo, mp.TransDate, mp.Credit, mp.Debit, row_number() over (partition by AccountNo order by TransDate) rn
from Money m
join CurrentPast cp
on m.accountNo = cp.AccountNo
and m.rn-1 = cp.rn)
select * from CurrentPast


^^ This will give you the each transaction as pairs. ^^


;with CurrentPast as
(select AccountNo, TransDate, Credit, Debit, cast(credit-debit as money) as CurrentChange, cast(0 as money) as PreviousBalance, row_number() over (partition by AccountNo order by TransDate) rn
from Money
union all
select mp.AccountNo, mp.TransDate, mp.Credit, mp.Debit, row_number() over (partition by AccountNo order by TransDate) rn
from Money m
join CurrentPast cp
on m.accountNo = cp.AccountNo
and m.rn-1 = cp.rn)



Now this is some sample code that shows you how to show a Running balance without ever changing it.  And it allows you to see a balance as of a point in time:



set nocount on
create table #tabMoney
(accountNo     int
,transDate     datetime
,Credit        money
,Debit         money
)
insert into #TabMoney values(1,'1/1/2007',0,20)
insert into #TabMoney values(1,'1/2/2007',20,0)
insert into #TabMoney values(1,'1/3/2007',0,15)
insert into #TabMoney values(1,'1/4/2007',10,0)
 
insert into #TabMoney values(2,'1/1/2007',0,100)
insert into #TabMoney values(2,'2/2/2007',0,50)
insert into #TabMoney values(2,'3/3/2007',160,0)
insert into #TabMoney values(2,'4/1/2007',0,10)
go
;with CurrentPast as
(
select t.* from 
(select AccountNo
     ,TransDate
     ,Credit
     ,Debit
     ,credit-debit as CurrentChange
     ,credit-debit as CurrentBalance
     ,row_number() over (partition by AccountNo order by TransDate) as rn
from #tabMoney) t
where t.rn=1
union all
select m.AccountNo, m.TransDate, m.Credit, m.Debit, m.CreditDebit, cp.CurrentChange+CreditDebit, m.rn
from 
     (select AccountNo
          ,TransDate
          ,Credit
          ,Debit
          ,credit-debit CreditDebit
          ,row_number() over (partition by AccountNo order by TransDate) rn
     from #tabMoney) m
  inner join CurrentPast cp
    on  m.accountNo = cp.AccountNo
    and m.rn-1 = cp.rn
)
select * from CurrentPast
Order by AccountNo,rn
go
drop table #tabMoney

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22610769
There are some assumptions based upon field names and structures.  But it can be easily adapted if you table structure is nothing like this.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22610891
I would agree with Brandon on not storing if you don't have to and if you want to use a common table expression and do have SQL 2005 you can modify the first query I gave you to do this:
WITH moneyCTE AS (
    SELECT *,
    row_number() over (ORDER BY transdate) AS rn
    FROM Money
)
UPDATE a
SET a.Balance = a.Credit - a.Debit + IsNull(b.balance, 0.00)
FROM moneyCTE a LEFT JOIN moneyCTE b
ON a.rn-1 = b.rn

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22610901
As Brandon, is saying here is he same query as a select instead of an update.
WITH moneyCTE AS (
    SELECT *,
    row_number() over (ORDER BY transdate) AS rn
    FROM Money
)
SELECT *, -- you can list out columns here
(a.Credit - a.Debit + IsNull(b.balance, 0.00)) AS newBalance
FROM moneyCTE a LEFT JOIN moneyCTE b
ON a.rn-1 = b.rn

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22610909
Same basic principal except I contained all of logic within the CTE to simplify the select.  6 & 1/2 dozen :)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22610948
True.  Was just extending my query and at first glance your query looked much different with the union all statement, but I see now where you were going with that.

Regards,
Kev
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

610 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