Results 1 to 2 of 2
Thread: Apportion Paid Amount

110811, 09:41 #1Registered User
 Join Date
 Jul 2011
 Posts
 4
Unanswered: Apportion Paid Amount
Suppose I have a transaction record like:
TransactionID LedgerID Period Amount Paid
1 100 1 500 300
2 100 2 200 150
3 100 3 100 100
The total amount for 3 periods = 800 and the total paid is 550. My task is to take the total paid and offset it from the very first transaction to calculate the balance
i.e For TransactionID = 1 Amount = 500 and total paid for the ledger is 550 so 500 can be offsetted here leaving a balance of 0
For TransactionID = 2 the Amount is 200, and we only have a paid amount of 50 left after offsetting TransactionID 1 so the balance of TransactionID 2 = 200  50 = 150
And for TransactionID = 3 we dont have any paid amount left so balance = 100  0 = 100
Is there any simple way of achieving this result using tsql query?
The other option I thought of using was a ratio of totalpaid/totalamount * individual amount but i end up having fraction of a cent (Assuming all the amount and paid are in cents), so offsetting is the approach i want to take.
Rule

We know that:
 totalamount = 800
 totalpaid = 550
Now we start creating running total (substraction) from the first transaction
i.e. First Transaction has Amount = 500. TotalPaid for the Ledger = 550. So we can take 500 out of 550 and put it against the first transaction to say it is paid, so balance = 0
Left totalpaid is 50 which is brought forward to the next transaction, so we apply 50 to this 2nd transaction and the balance left is 150
For 3rd transaction, we don't have any balance left as we applied 500 & 50 to 1st and 2nd transactions respectively, so paid remains as 0 and balance remains as 100
Expected Result

Code:TransactionID LedgerID Period Amount Paid Balance       1 100 1 500 500 0 2 100 2 200 50 150 3 100 3 100 0 100

110911, 02:17 #2Registered User
 Join Date
 Sep 2011
 Location
 Greenville, SC USA
 Posts
 34
RE: Apportion Paid Amount
Using SS2005 and an rCTE, the following is my initial stab at it, which can be improved upon, I'm sure, but, here it goes... (all table names have been changed to protect the innocent )
Code:declare @tt table ( TransactionID INT IDENTITY(1,1) NOT NULL ,LedgerID INT NOT NULL ,Period INT NOT NULL ,Amount MONEY NULL ,Paid MONEY NULL ); insert into @tt (LedgerID ,Period ,Amount ,Paid) select 100 ,1 ,500.00 ,300.00 union all select 100 ,2 ,200.00 ,150.00 union all select 100 ,3 ,100.00 ,100.00 ;with tba as ( select LedgerID ,sum(Paid) as TBA from @tt group by LedgerID ) ,rcte as ( select t.TransactionID ,t.LedgerID ,t.Period ,t.Amount ,case when r.TBA < t.Amount then cast(0.00 as money) else r.TBA  t.Amount end as TBA ,case when r.TBA > t.Amount then t.Amount else r.TBA end as Paid ,r.TBA  (case when r.TBA > t.Amount then t.Amount else r.TBA end) as Balance from tba r ,@tt t where t.LedgerID = r.LedgerID and t.TransactionID = 1 union all select t.TransactionID ,t.LedgerID ,t.Period ,t.Amount ,case when r.TBA < t.Amount then cast(0.00 as money) else r.TBA  t.Amount end ,case when r.TBA > t.Amount then t.Amount else r.TBA end ,r.TBA  (case when r.TBA > t.Amount then t.Amount else r.TBA end) from rcte r ,@tt t where t.LedgerID = r.LedgerID and t.TransactionID = r.TransactionID + 1 ) select TransactionID ,LedgerID ,Period ,Amount ,Paid ,Balance from rcte