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 ;
?>

‹ Account TableupCategory Table ›