Running Balance

[joshmac]

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  |

Does this have to be done (balance) in MySQL or can it be done in a server-side language like PHP? If it can, then it should be a simple solution. Otherwise, I think you may need to use a SET variable in your query.

For example: http://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql

If in PHP, the output data can be calculated in a loop.

[joshmac]

I saw that stackoverflow thread when I first researched this and what I am trying to avoid is using the SET variable.

Also, thanks for asking the question because I am actually coding in PHP. So the answer to your question is yes, it can be done in PHP and/or combination of both, and I thought of that as an alternative solution. I just haven't figured out the best way to do it in either circumstance yet.

You could benchmark each and then decide. I shouldn't have thought there'd be much in it for a small dataset.

$balance = 0;
$output = ''
while($row = $stmt->fetch(PDO::FETCH_NUM))
{
    $balance += (floatvar($row[4]) - floatvar($row[3]));
    $row[5] = $balance;
    $output .= '<tr><td>' . implode('</td><td>', $row) . '</td></tr>';
}

I don't usually use FETCH_NUM, but if the SQL fields in your query are in the same order as you wish to show in your HTML table, then it could be a quick and dirty method (the 'implode method').

<table>
    <...head row ..>
    <?=$output?>
</table>

[joshmac]

Thanks, I will give it a go.