I am not sure if this is possible or not, but I need help writing a query for a running balance report. I tried a few queries on my own; I've come close but not exactly what I need.
For convenience, I've created an sqlfiddle: Click Here
The report needs to show personID, date, description, fee, amount, balance. Both fees and payments need to show on the report and be interspersed. The date on the report will be either the fee date or payment date, and the balance on the report needs to be the balance minus any payment if it exists. If my explanation is unclear, please ask. Hopefully, the sample report below can explain it better.
| personID | date | description | fee | payment_amount | balance |
=================================================================================
| 1 | 2014-03-26 | Check Reversal | 25.00 | | -25.00 |
| 1 | 2014-04-20 | Lab Fee | 30.00 | | -55.00 |
| 1 | 2014-04-21 | Material Fee | 10.00 | | -65.00 |
| 1 | 2014-04-27 | | | 20.00 | -45.00 |
| 1 | 2014-05-01 | | | 30.00 | -15.00 |
| 1 | 2014-05-15 | Late Fee | 15.00 | | -30.00 |
| 1 | 2014-05-15 | | | 12.00 | -18.00 |
| 1 | 2014-05-18 | Library Fee | 15.00 | | -33.00 |
| 1 | 2014-05-28 | | | 5.00 | -28.00 |
| 1 | 2014-05-30 | Security Deposit | 225.00 | | -253.00 |
| 1 | 2014-06-01 | Room Damage | 50.00 | | -303.00 |