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:
| TransactionDate | CardID | TranIdADD | TranIdSub | TranAmount |
|---|
| 01/01/2013 | 1 | 1 | | 100 |
| 02/01/2013 | 1 | 2 | | 50 |
| 03/01/2013 | 1 | | 3 | -75 |
| 01/02/2013 | 1 | | 4 | -50 |
| 02/02/2013 | 2 | 5 | | 100 |
| 02/01/2013 | 2 | | 6 | -80 |
and the result must be something like:
| TransactionDate | CardID | TranIdADD | TranIdSub | TranAmount | CalculatedTranAmount |
|---|
| 01/01/2013 | 1 | 1 | 4 | 100 | 0 |
| 02/01/2013 | 1 | 2 | | 50 | 25 |
| 03/01/2013 | 1 | 1 | 3 | -75 | -75 |
| 01/02/2013 | 1 | 2 | 4 | -50 | -25 |
| 02/02/2013 | 2 | 5 | | 100 | 20 |
| 02/01/2013 | 2 | 5 | 6 | -80 | 20 |
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
