Home Forums SQL Server 2008 T-SQL (SS2K8) Looping through table RE: Looping through table

  • September 9, 2012 at 10:42 pm

    #1535537

    jadelola (9/8/2012)

    Hi,

    I have to write a query and i am not sure how to go about it. I have a table customer with fields customerid and amount (decimal) and based on parameters I have to update the amount of each customer.

    The total amount can't exceed $XX.XX (eg $250.000).

    If user decides that he wants to buy all the amount that each user has than the total he buys can't exceed $250.000. So that means in the example below that he can buy from customers 1-3 all the amount but from the last customer he can only buy 160,000 has 20,000+20,000+30,000+130,000=290 and if from the last customer he would buy 200,000 the total will exceed the limit of 250.

    The user doesn't select which customer he wants to buy from. It's random. Maybe I will order it by ID.

    How can I write the stored procedure?

    Example:

    ID AMOUNT AmountPurchased

    1 20,000

    2 30,000

    3 40,000

    4 200,00

    Thanks

    I like to test my answers before I post them but don't always have the time to setup the problem. Take a look at the first link in my signature line below. If you post your data that way, you'll get much better answers more quickly.

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