0

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  |
2
Contributors
4
Replies
22
Views
2 Years
Discussion Span
Last Post by joshmac
0

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.

0

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>

Edited by diafol

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.