This was a fun procedure to write. I originally found the IFNULL function for my Account Balance Store Procedure. This query, when directly implemented in PHP was MUCH more complicated because of the possibilities of returning a NULL value. Now, it's all done in one statement.

<?php
--
-- Transaction Open Batches Stored Procedure
--

USE `cics_app`;

DROP PROCEDURE IF EXISTS Transaction_OpenBatches;

DELIMITER |
CREATE PROCEDURE Transaction_OpenBatches (IN batch_account_id INT)
BEGIN
    SELECT
        `transaction`.`batch_number` AS batch_no,
        IFNULL(
            IFNULL((
                SELECT SUM(`transaction`.`amount`) FROM `transaction`
                WHERE `batch_number` = `batch_no` AND `credit_account_id` = batch_account_id
            ),0)
        -
            IFNULL((
                SELECT SUM(`transaction`.`amount`) FROM `transaction`
                WHERE `batch_number` = `batch_no` AND `debit_account_id` = batch_account_id
            ),0)
        ,0) AS batch_balance
    FROM
        `transaction`
    WHERE
        `transaction`.`batch_number` > 0
    GROUP BY
        `transaction`.`batch_number`
    HAVING
        batch_balance > 0;
END
|

DELIMITER ;
?>

‹ Transaction Max Batch Number Stored ProcedureupTransaction Open Units Stored Procedure ›