0 Replies Latest reply: Sep 11, 2013 9:48 AM by Tiu Adrian RSS

    Please help

      Hi all,

      please help me with a problem.

      i need to have an script that will calculate FIFO the balance of a card. The card can have multiple transaction, each transaction will add or remove an certain amount of money from the card, and i need to mach the transaction id's depending if the removing transaction is consuming the entire add transaction or not. Here is an example:

      The initial table:

      TransactionDateCardIDTranIdADDTranIdSubTranAmount
      01/01/201311100
      02/01/20131250
      03/01/201313-75
      01/02/201314-50
      02/02/201325100
      02/01/201326-80

       

      and the result must be something like:

      TransactionDateCardIDTranIdADDTranIdSubTranAmountCalculatedTranAmount
      01/01/20131141000
      02/01/2013125025
      03/01/2013113-75-75
      01/02/2013124-50-25
      02/02/20132510020
      02/01/2013256-8020

       

      To explain better:

      for card with ID = 1

           - transaction with ID = 3 remove from transaction with ID = 1 the amount and remains 25 (100-75=25) and the TranIdADD is updated with 1

           - transaction with ID = 4 remove from transaction with ID = 1 all amount and remains 0 and also from transaction with ID = 2 the rest of 25. On the transaction with ID = 1 remains 0 and on the transaction with ID = 2 remains 25. For the transaction with Id = 4, the TranIdAdd is updated with 2 (because that transaction was covering the entire amount). For the transaction with the ID = 1, the TranIdSUB is updated with 4 (the transaction id that finished the entire amount). The transaction with ID = 2 remains with TranIdSub =  null/nothing because it has amount, and the amount (25) represents the amount of the card