Results 1 to 2 of 2
  1. #1
    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 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. #2
    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
  •