Home Forums SQL Server 2008 SQL Server 2008 - General Running Balance Updation RE: Running Balance Updation

  • April 26, 2015 at 2:24 pm

    #1793545

    vikramchander90 (4/26/2015)

    I have below table and few rows as eg

    Create Tableledger

    (

    row_noint,

    acct_idvarchar(255),

    debitnumeric(28,2),

    creditnumeric(28,2),

    balancenumeric(28,2)

    )

    row_noacct_idtran_descdebitcreditbalance

    0Account1Opening BalanceNULLNULL1000.00

    0Account2Opening BalanceNULLNULL500.00

    1Account1SOBI0.00200.00NULL

    2Account1SNP300.000.00NULL

    1Account2SNP100.00200.00NULL

    I need to update the balance column as running balance based on acct_id column as previous balance + debit - credit having row_no > 0

    row_noacct_idtran_descdebitcreditbalance

    0Account1Opening BalanceNULLNULL1000.00

    0Account2Opening BalanceNULLNULL500.00

    1Account1SOBI0.00200.00800.00

    2Account1SNP300.000.001100.00

    1Account2SNP100.00200.00400.00

    I have achieved this using While loop by including sno column in table. But it is taking long time to process over 9500 rows and also by Cursor

    Is there any quicker way to achieve this?

    Yes... as Eirikur has suggested, there is a MUCH quicker way that will update a million rows in about 3 seconds. But, before I can show you how to do it, I absolutely need to know what you currently have for the PK of the table AND what you have for the Clustered Index of the table.

    --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)