I'm still working on the project mentioned here (http://dba.stackexchange.com/questions/2428/how-do-i-properly-design-a-many-to-many-charges-payments-accounting-system).
The system is to give the user the option to either pay specific amounts on specific charges, or make generic "you figure it out" payments. Given the table structure we went with (PAYMENTS, CHARGES, PAYMENTS_TO_CHARGES) I'm looking to cheat a bit. Basically I'm looking for an amazingly snazzy "reconcile" SQL query that will do the following:
STEP 1) Grab all Payments with remaining balance (basically credits)
STEP 2) Grab all Charges with remaining balance (partially paid, etc)
STEP 3) Insert portions of payments in the PAYMENTS_TO_CHARGES table until there is no more available credit or there are no more charges.
…I guess technically this isn't reconciliation so much as creating transactional data, but you get the idea.
Steps 1 and 2 are obviously very easy. It's Step 3 that's the killer. If there's no snazzy way to do this in SQL, I suppose I'll go with the old hand-coded step-by-step loop-through-each transaction and post payment_to_charge…just thought I'd ask.
Thanks in advance!
EDIT 1:
I've come up with this query to determine which charges have remaining balance, but it's giving me an error saying "Unknown column 'remaining_balance' in 'where clause'":
SELECT
charges.*
, (charges.amount - transactions.total_paid) as remaining_balance
FROM charges
, (SELECT
charge_id
, sum(amount) as total_paid
FROM payments_to_charges
GROUP BY charge_id) as transactions
WHERE charges.member_id = 123
AND charges.id = transactions.charge_id
AND remaining_balance > 0
AND charges.active_on < NOW()
I'm sure certain items are simply out of order, but I can't figure out what's generally wrong with this particular query. Should I be using HAVING instead of WHERE? Am I missing something else completely obvious?
Best Answer
You might be able to do the INSERT into Payemnts_To_Charges in one SQL statement, but I'm not sure it would be worth it. It seems like this would be easier to build, debug, and maintain in procedural code. Something like this:
Update:
remaining_balance is the issue. You can't reference the aliased value in the WHERE clause. You could either make the query a sub-query and add that condition on a higher level or change remaining_balance in the WHERE clause for (charges.amount - transactions.total_paid).