Calculating balance from previous row

Question

Calculating balance from previous row

SQL006

SSCertifiable

Points: 5071
More actions
December 27, 2010 at 4:27 am

#229096

xx

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Hi Dilip,

If you could post some test data (see the article at the first link in my signature line below to help us help you the best way) to go along with your test table in your original post, I'm sure that someone could demo a "Quirky Update" with a built in safety check. It's MUCH faster (a million rows in just a couple of seconds) than any other method and, with the safety check (not covered in the following article), it guaranteed to work every time even if MS get's froggy with a future change.

http://www.sqlservercentral.com/articles/T-SQL/68467/

I recommend that don't strike out on your own on this one even after reading the article above. Post the data in a readily consumable format as previously requested and let us help you with it so we can include the safety check for you.

Otherwise, a nice Read_Only, Forward_Only, Local, Static cursor would be your best bet (about 2 to 6 minutes for a million rows).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Abhijeet Chavan SSCommitted Points: 1511 More actions · Answer 2

Add this code in ur proc

declare @crds_DueAmount_prev float

select top 1 @crds_DueAmount_prev = crds_DueAmount from ISV_CreditSettlement

WHERE crdp_CreditDepositID = @crds_CreditDepositID

order by crds_CreditSettlementID desc

--get the due amount

DECLARE @crds_DueAmount float

SET @crds_DueAmount = coalesce(@crds_DueAmount_prev,@crds_UploadedAmount) - @crds_SettledAmount --this is where i want the previous balance.

And try if this work