Safe/correct way to reduce balance from account

  • developerlearn999

    SSC Enthusiast

    Points: 112

    i have a table:

    id int,customerid int,balance.

    i want to reduce for a "customerid" from his balance,assuming his balance-@amount to reduce>=0.

    what is the corret/safe way to do it, assuming the reduce can be called more then once at the same time from different applications?

  • ScottPletcher

    SSC Guru

    Points: 98079

    UPDATE dbo.table_name

    SET balance = balance - @amount

    WHERE balance - @amount >= 0.0

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Matt Miller (4)

    SSC Guru

    Points: 124180

    The "safe" way is not to store the balance itself.  store the transactions increasing or decreasing the balance, and calculate the balance when you need to see it.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • ScottPletcher

    SSC Guru

    Points: 98079

    I've had the same bank account for more than 20 years.  I'd hate to have to wait for them to compute a balance every time.

    A balance is one of those things you sometimes just have to maintain directly, even though it's technically redundant.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Jeff Moden

    SSC Guru

    Points: 994523

    ScottPletcher wrote:

    I've had the same bank account for more than 20 years.  I'd hate to have to wait for them to compute a balance every time.

    A balance is one of those things you sometimes just have to maintain directly, even though it's technically redundant.

    Having the balance readily available is a wonderful thing, I agree... having a table like the op's is a horribly unsafe way to do things UNLESS there's a transaction table to back it up where the balance could be totally regenerated if some nub makes a mistake.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • RonKyle

    SSC-Dedicated

    Points: 31456

    've had the same bank account for more than 20 years.  I'd hate to have to wait for them to compute a balance every time.

    A balance is one of those things you sometimes just have to maintain directly, even though it's technically redundant.

    No, it's not automatically one of those things.  I've calculated balances on millions of rows at think time.  Hopefully you at least tried it out and only on finding that you could not do it at a speed required by the operations staff that you then resorted to this denormalization.

  • Jeff Moden

    SSC Guru

    Points: 994523

    RonKyle wrote:

    've had the same bank account for more than 20 years.  I'd hate to have to wait for them to compute a balance every time.

    A balance is one of those things you sometimes just have to maintain directly, even though it's technically redundant.

    No, it's not automatically one of those things.  I've calculated balances on millions of rows at think time.  Hopefully you at least tried it out and only on finding that you could not do it at a speed required by the operations staff that you then resorted to this denormalization.

    For banks, etc, they'll usually do something like a 3 month window in a DW where the balance of all that has previously occurred prior to that 3 month window (or year window or whatever... is sometimes a list of balances by year and month which can quickly be summarized).  They don't calculate the current balance by running through 20 years of transactions.  They use previously "frozen" balances and then calculate what the current transactions are and do the math on the previous balance.

    My point was what the OP wrote for the posted problem.  It appeared that he was trying to maintain the only balance by modifying it directly.  That's just not the right way to do it.  You MUST have transactions that support what the balance actually is and, yes, that can be a single balance from previously "Closed" transaction periods that will NEVER again change plus the current transaction in the currently "Open" transaction period.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • RonKyle

    SSC-Dedicated

    Points: 31456

    They use previously "frozen" balances and then calculate what the current transactions are and do the math on the previous balance.

    Agreed.  Most balances with which I've worked have opening and closing periods outside the normal fiscal periods.  For any given year, you just need a YTD function that includes the opening balance.  The few companies that don't do this are working outside the norm and a different method is required unless you are going to do something behind the scenes.

  • Michael L John

    One Orange Chip

    Points: 25729

    developerlearn999 wrote:

    i have a table:

    id int,customerid int,balance.

    i want to reduce for a "customerid" from his balance,assuming his balance-@amount to reduce>=0.

    what is the corret/safe way to do it, assuming the reduce can be called more then once at the same time from different applications?

    Is this homework?

    If so, as everyone else has said, this is probably not a great idea from a design perspective.

    Now, the teacher may be testing you to see if you can figure out the issues related to this kind of architecture.  And now you have some good answers for him!

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • ScottPletcher

    SSC Guru

    Points: 98079

    Many companies have clients that have a credit limit: they can't order over a certain amount without prior approval. [I've worked in 2 such companies at least already.]  That "remaining balance for orders on credit" must be a running total, because orders could come in at any time.  Likewise, your bank must know your exact current balance so it can allow/deny an ATM withdrawal correctly.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • terry999

    SSCarpal Tunnel

    Points: 4786

    Could you use an Indexed view to maintain the balance?

Viewing 11 posts - 1 through 11 (of 11 total)

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