Apportion Paid Amount - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Apportion Paid Amount

Author

Topic

rka
Starting Member

6 Posts

Posted - 2011-11-08 : 08:46:04

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

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 09:01:57

[code]
SELECT TransactionID, LedgerID, Period,Amount,CASE WHEN LedgerSum - COALESCE(totAmt,0) > totAmt THEN totAmt ELSE CASE WHEN LedgerSum - COALESCE(totAmt,0) < 0 THEN 0 ELSE LedgerSum - COALESCE(totAmt,0) END END AS Paid,Amount-CASE WHEN LedgerSum - COALESCE(totAmt,0) > totAmt THEN totAmt ELSE CASE WHEN LedgerSum - COALESCE(totAmt,0) < 0 THEN 0 ELSE LedgerSum - COALESCE(totAmt,0) END END AS Balance
FROM
(
SELECT TransactionID, LedgerID, Period,Amount, Paid,SUM(Paid) OVER (PARTITION BY LedgerID ) AS LedgerSum,q.totAmt
FROM table p
CROSS APPLY (SELECT SUM(Amount ) AS totAmt
FROM table
WHERE LedgerID=t.LedgerID
AND TransactionID < t.TransactionID
)q
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources