Account Balance Stored Procedure
This is one of the very first procedures I wrote. It simply makes two queries, adding up the debits and subtracting the credits.
<?php
--
-- Account Balance Stored Procedure
--
DROP PROCEDURE IF EXISTS Account_Balance;
DELIMITER |
CREATE PROCEDURE Account_Balance (IN account_id INT, IN before_date DATE)
BEGIN
DECLARE acct_credit, acct_debit, balance INT;
SELECT
SUM(t.amount) INTO acct_credit
FROM
transaction AS t
WHERE
t.credit_account_id = account_id
AND
t.date <= before_date
ORDER BY
t.created DESC, t.transaction_id ASC;
SELECT
SUM(t.amount) INTO acct_debit
FROM
transaction AS t
WHERE
t.debit_account_id = account_id
AND
t.date <= before_date
ORDER BY
t.created DESC, t.transaction_id ASC;
SET balance = acct_debit - acct_credit;
SET balance = IFNULL(balance,0);
SELECT balance;
END;
|
DELIMITER ;
?>Taxonomy upgrade extras:
Mike Hostetler is an inventor, entrepreneur, programmer and proud father. Having worked with web technologies since the mid 1990's, Mike has had extensive experience developing web applications with PHP and JavaScript. Currently, Mike works as the CEO of 
