Post reply

Calculating balance from previous row

  • December 27, 2010 at 4:27 am

    #229096

    xx

  • December 27, 2010 at 7:35 am

    #1266440

    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://qa.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)
    Intro to Tally Tables and Functions

  • December 29, 2010 at 5:50 am

    #1267105

    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

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

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