title: mySQL debit credit balances
body: <p>Is it possible to have a mySQL query to get these fields old balance (sum of my expenses), debit (here should I put total my expenses with same id in the current date), credit (not much in these query), new balance (same as debit). </p>
  
  <p>Now when the day was ended, the new balance will be added to the old balance, and a new balance will be form for the current date. I have my code but it does not calculate my previous balances. </p>
  
  <p>Table: rf_expenses</p>
  
  <pre><code>--------------------------------------
  id_rf_expenses   rf_expense_desc
  --------------------------------------
  1                    salary
  2                    bonus
  3                    transportation
  </code></pre>
  
  <p>Table: expenses</p>
  
  <pre><code>-----------------------------------------------------------
  id_expnses    id_rf_expenses     expense_amt   expense_date
  ------------------------------------------------------------
  1                1                  100           current
  2                1                  100           yesterday
  3                2                   50           current
  4                2                   50           current
  5                3                  200           yesterday
  </code></pre>
  
  <p>OUTPUT: </p>
  
  <pre><code>-----------------------------------------------------------
  EXPENSE      OLD BALANCE    DEBIT     CREDIT   NEW BALANCE
  -----------------------------------------------------------
  SALARY           100         100        0          100          
  BONUS             0          100        0          100
  TRANSPO          200           0        0            0
  </code></pre>
  
  <p>This will happen every query of the current date..any dates previous the the current will be sum up in the old balance</p>
  
  <pre><code>SELECT 
      r.rf_expense_desc, 
      COALESCE(SUM(expense_amt), 0) - COALESCE(q1.amt2, 0) OLD, 
      COALESCE(q1.amt2, 0) AS NEW
  FROM 
      rf_expenses r
  LEFT JOIN 
      expenses e ON r.id_RF_expense = e.id_rf_expense
  LEFT JOIN 
      (SELECT 
           SUM(expense_amt) amt2, id_expense, 
           rf_expenses.id_rf_expense
       FROM 
           rf_expenses
       LEFT JOIN 
           expenses ON rf_expenses.id_RF_expense = expenses.id_rf_expense
       WHERE 
           expense_date = CURDATE( ) 
       GROUP BY 
           rf_expense_desc, expense_date) q1 ON r.id_rf_expense = q1.id_rf_expense
  GROUP BY 
      rf_expense_desc 
  </code></pre>