Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2009
    Posts
    1

    Unanswered: Need to modify to handle more than one account

    Hi a kind fellow provided me a solution for this in another forum. I'm just a beginner in sql and don't know how I can modify the solution he provided me to handle more than one account.

    Please can anyone help!

    Here is the solution.
    =================================
    Here's something to start us off...


    --structure and data
    declare @Charges table (Account int, TransactionDate datetime, DueDate datetime, TransAmount money)
    insert @Charges
    select 1000, '01/01/2009', '02/01/2009', $100.00
    union all select 1000, '01/02/2009', '02/02/2009', $150.00
    union all select 1000, '01/03/2009', '02/03/2009', $450.00
    union all select 1001, '01/03/2009', '02/03/2009', $10
    --/

    --inputs
    declare @Account int, @Payment money
    set @Account = 1000
    set @Payment = $200.00
    --/

    --calculation
    ; with
    t1 as (select *, row_number() over (order by TransactionDate) as Row from @Charges where Account = @Account)
    , t2 as (select a.Row, a.TransAmount, sum(b.TransAmount) as CumulativeTransAmount
    from t1 a inner join t1 b on a.Row >= b.Row group by a.Row, a.TransAmount)
    , t3 as (select a.*, case when b.CumulativeTransAmount - @Payment < 0 then 0
    when b.CumulativeTransAmount - @Payment < b.TransAmount then b.CumulativeTransAmount - @Payment
    else b.TransAmount end as NewTransAmount
    from t1 a inner join t2 b on a.Row = b.Row)
    update t3 set TransAmount = NewTransAmount where TransAmount != NewTransAmount
    --/

    --results
    select * from @Charges
    /*
    Account TransactionDate DueDate TransAmount
    -------- ---------------- ----------- ---------------------
    1000 2009-01-01 2009-01-02 0.00
    1000 2009-02-01 2009-02-02 50.00
    1000 2009-03-01 2009-03-02 450.00
    1001 2009-03-01 2009-03-02 10.00

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    on the other forum, why don't you ask the person who gave it to you to explain it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •