I have an accounting transaction table with the following fields:
- transactionid
- date
- transactiontype (charge,payment,credit,etc)
- debitaccount
- creditaccount
- amount
And I have the following records
id date type debaccount credaccount amount
-- ---------- ------- ---------- ----------- ------
1 02/13/2012 charge utilities receivables $200.00
2 02/17/2012 charge maintenan receivables $150.00
3 03/06/2012 credit receivables utilities ($20.00)
4 03/27/2012 charge repairs receivables $80.00
5 04/29/2012 charge utilities receivables $135.00
6 05/21/2012 charge utilities receivables $70.00
7 06/22/2012 charge utilities receivables $60.00
8 07/19/2012 payment receivables utilities ($100.00)
Right now I can get the balance without problem, so I know:
Account Utilities Income 200-20(credit)+135+70+60 = 445 - 100 (payment) = Balance $345
Now what I need is to get the outstanding balance by due days for example:
Account 0-30 days 31-60 days 61-90 days 90+ days Balance
--------- --------- ---------- ---------- --------- ----------
receivables $60.00 $70.00 $135.00 $200.00 $465.00
($20) $445.00
($100) $345.00
I have seen that when you have an outstanding balance, if you make a payment, that payment will be affecting the very last charge (as seen in the table). My question is if I need to have a relation between transactions and transactions so I will know that one payment is affecting "x" number of charges. This is the only solution it came into my mind in order to get the desired outstanding balance by due days.
Any help will be greatly appreciated!!
Thanks
