Home Forums SQL Server 2008 T-SQL (SS2K8) sql query RE: sql query

  • September 2, 2013 at 12:50 pm

    #1646795

    ramya.usapp (9/1/2013)

    Hello friends,

    Please help me in writing a query!!

    Question:

    Write a query based off of the following data stored in the AccountingEntry table. The query needs to return running totals and should produce the exact results for each customer listed below.

    Deposits – amounts added to an account

    Charge – amounts should be subtracted from a deposit

    Cancel/Refund – amount returned to a customer, should be subtracted from a deposit

    AccountingEntryIDCustomerIDDateTypeAmount

    111/1/2013Deposit400

    231/23/2013Deposit900

    3192/28/2013Deposit250

    4233/15/2013Charge175

    512/1/2013Charge350

    6154/1/2013Deposit2000

    732/23/2013Charge500

    8154/1/2013Charge100

    912/23/2013Deposit100

    10233/15/2013Charge175

    11154/1/2013Charge750

    1212/15/2013Charge25

    13154/1/2013Cancel/Refund1150

    1431/25/2013Deposit100

    15154/1/2013Deposit750

    16193/28/2013Charge100

    17154/1/2013Deposit100

    1833/23/2013Charge500

    19233/15/2013Deposit400

    20194/28/2013Charge100

    2113/15/2013Cancel/Refund125

    Results should look like

    AccountingEntryIDCustomerIDDateTypeAmountRunning DepositRunningAmount ChargedRunningAmt Remaining

    111/1/2013Deposit4004000400

    512/1/2013Charge35040035050

    1212/15/2013Charge2540037525

    912/23/2013Deposit100500375125

    2113/15/2013Cancel/Refund1255003750

    Thanks,

    RT

    Since this is homework, you must have an instructor. There are 3 typical ways of solving this problem in SQL Server 2008... Cursor/While Loop, Temp Table/While Loop, Correlated Sub-Query (which would form a "Triangular Join" which is very bad but the instructor may be teaching it).

    There' also an atypical method known as the "Quirky Update" which will blow all 3 of those methods out of the water but it's not likely that your instructor taught it or even knows anything about it. Same goes for another atypical method (the "Multi-Pass" update) that a chap by the name of "Hugo" wrote.

    With all that in mind, my question to you is what has your instructor recently covered out of the 5 things mentioned above so that we can play into what the instructor actually wants to see?

    Also, if you want much better help, please see the first "Helpful Link" in my signature line below.

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