1. Registered User
Join Date
Jul 2011
Posts
4

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 t-sql 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```

2. Registered 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```

Posting Permissions

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