<?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
trxn AS t
WHERE
t.credit_account_id = account_id
AND
t.date <= before_date
ORDER BY
t.created DESC, t.trxn_id ASC;
SELECT
SUM(t.amount) INTO acct_debit
FROM
trxn AS t
WHERE
t.debit_account_id = account_id
AND
t.date <= before_date
ORDER BY
t.created DESC, t.trxn_id ASC;
SET balance = acct_debit - acct_credit;
SET balance = IFNULL(balance,0);
SELECT balance;
END;
|
DELIMITER ;
?>
