Trial Balance Update accounts

SELECT * FROM DATES d, BALANCE b
WHERE d.date >= b.date
      AND b.date >= ALL (SELECT b1.date FROM BALANCE b1
                    WHERE b1.date <= d.date
                          AND b1.account = b.account)

SELECT d.date, b.account, b.balance FROM DATES d, BALANCE b
WHERE d.date >= b.date
      AND b.date >= ALL (SELECT b1.date FROM BALANCE b1
                         WHERE b.account = b1.account
                               AND d.date >= b1.date)
ORDER BY d.date, b.account

select accno, accopbal, sum(mtd_d), sum(mtd_c), sum(ytd_d),sum(ytd_c)
from 
(   select ao.accno
         , ao.accOpBal
         , 0 as mtd_d
         , 0 as mtd_c
         , 0 as ytd_d
         , 0 as ytd_c
    from accounts ao
    left outer join transactions tn on tn.accno = ao.accno
    where tn.accno is null
    union
    select tm.accno
         , a.accOpBal
         , sum(tm.transdebit) as mtd_d
         , sum(tm.transcredit) as mtd_c
         , 0 as ytd_d
         , 0 as ytd_c
    from accounts a
    right outer join transactions tm on tm.accno = a.accno
    where tm.transdt between '2014-01-01' and '2014-01-31'
    group by a.accno, a.accopbal
    union 
    select ty.accno
         , a.accOpBal
         , 0
         , 0
         , sum(ty.transdebit)
         , sum(ty.transcredit)
    from accounts a
    right outer join transactions ty on ty.accno = a.accno
    and ty.transdt between '2013-07-01' and  '2014-01-31'
    group by a.accno, a.accopbal
) alltxn
group by accno, accopbal

-- january
insert into transactions values (1, 'alfki', '2014-01-01', 1,3); 
insert into transactions values (1, 'alfki', '2014-01-02', 1,3); 
insert into transactions values (1, 'alfki', '2014-01-03', 1,3); 

-- last year
insert into transactions values (1, 'alfki', '2013-09-01', 5,2);

-- txn without acc
insert into transactions values (1, 'noexi', '2014-01-03', 4,2); 


-- acc with txn
INSERT INTO Accounts values ( 'alfki', 'alfred', 4);
-- acc without txn
INSERT INTO Accounts values ( 'lefto', 'lefto', 6);

ACCNO | ACCOPBAL |SUM(MTD_D)|SUM(MTD_C)|SUM(YTD_D)|SUM(YTD_C)
------+----------+----------+----------+----------+-----------
alfki |  4       |      3   |     9    |    8     |   11
lefto |  6       |      0   |     0    |    0     |    0
noexi | (null)   |      4   |     2    |    4     |    2

//this array will hold bank and cash values
$main_acc=array('bank'=>array('Dr'=>0,'Cr'=>0), 'cash'=>array('Dr'=>0,'Cr'=>0));

//this one to hold other accounts and their amounts, transaction etc.
$acc = array();
while($rowin = mysqli_fetch_array($rein, MYSQLI_ASSOC)) {
    /* store bank transaction type and amount */
    if($rowin['paymode']=='Cheque'){
        $main_acc['bank'][$rowin['transaction']] =$main_acc['bank'][$rowin['transaction']] + $rowin['amount'];
    }
    /* store cash transaction type and amount */
    if($rowin['paymode']=='Cash'){
        $main_acc['cash'][$rowin['transaction']] = $main_acc['cash'][$rowin['transaction']] + $rowin['amount'];
    }
    /* store other account transaction type and increment
       amount if already exists */
    if(isset($acc[$rowin['account']])){
        $acc[$rowin['account']]['amount'] = $acc[$rowin['account']]['amount'] +$rowin['amount'];
    }
    else
    {
        $acc[$rowin['account']]['trans'] = $rowin['transaction'];
        $acc[$rowin['account']]['amount'] = $rowin['amount'];

    }

}

echo '
<table>
<tr>
    <td align="left"><b>ACCOUNT</b></td>
    <td align="right"><b>DEBIT</b></td>
    <td align="right"><b>CREDIT</b></td>
</tr>
';

$bg = '#eeeeee';
//displaying bank and cash
foreach($main_acc as $key=>$val){

            $bg = ($bg=='#eeeeee') ? '#ffffff' : '#eeeeee';
            echo '<tr bgcolor="'. $bg .'">
                    <td align="left">'. $key .'</td>
                    <td align="left">'. (($val['Cr']!=0)?$val['Cr']:'') .'</td>
                    <td align="right">'. (($val['Dr']!=0)?$val['Dr']:'').'</td>
                  </tr>
        ';
}

//displaying other accounts
foreach($acc as $key=>$val){

            $bg = ($bg=='#eeeeee') ? '#ffffff' : '#eeeeee';
            echo '<tr bgcolor="'. $bg .'">
                    <td align="left">'. $key .'</td>
                    <td align="left">'. (($val['trans']=='Dr')?$val['amount']:'') .'</td>
                    <td align="right">'. (($val['trans']=='Cr')?$val['amount']:'') .'</td>
                  </tr>
        ';
}
echo '</table>';

ACCOUNT DEBIT   CREDIT
bank    5000    
cash    2000      500
TUITION          7000
Repairs 500

SELECT account_number,
       Balance,
       `0-30` - (total_invoices - Balance - LEAST(`90+` + `60-90` + `30-60`, total_invoices - Balance)) AS `0-30`,
       `30-60` - (total_invoices - Balance - LEAST(`90+` + `60-90`, total_invoices - Balance)) AS `30-60`,
       `60-90` - (total_invoices - Balance - LEAST(`90+`, total_invoices - Balance)) AS `60-90`,
       GREATEST(0, `90+` - (total_invoices - Balance)) AS `90+`
FROM (...)