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)
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
declare @Account int, @Payment money
set @Account = 1000
set @Payment = $200.00
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