Calculate closing balance month by month

Question

I want to calculate the closing balance month by month. I have two tables: tblOpenBalance and tblSales. I want this output:

id      Year    Jan         Feb         Mar         Apr         May         Jun         Jul         Aug         Sep         Oct         Nov         Dec     
245854  2019    38105.40    112174.00   98299.00    79053.00    65052.00    61025.57    47511.44    49284.06    99796.15    102556.91   57226.55    82651.33
191265  2018    41049.00    87576.00    85500.00    62385.00    86934.60    55784.00    70604.00    40824.00    37746.78    54491.42    67902.00    93663.80
146002  2017    36461.63    83386.21    84303.27    113673.00   53539.00    57599.75    55795.20    51738.80    44694.01    62729.00    57032.00    76535.40

The value of each month is calculated from the following elements:

Value from tblOpenBalance
Sum of sales of debit from previous months - sum of sales of credit from previous months (if i want to get closing balance of February 2020, this sum is sum of sales where date < February 2020)
sum of sales (debit - credit) from current month

The final formula should be: Value From tblOpenBalance + (Sum of sales of debit from previous months - sum of sales of credit from previous months) + sum of sales (debit-credit) from current month. How can I write it in mysql in one query for each month in year, but not with 12 queries.

tblOpenBalance:

ID     Code      Amount
1      503       1000
2      401       2000

tblSales

CodeDebit   CodeCredit  date    Amount  
503 411 2020-03-25  564.000 
503 411 2020-01-25  123.000 
503 411 2020-01-25  23.000  
503 411 2020-01-25  240.000 
503 411 2020-01-25  36.000  
503 411 2020-01-25  242.000 
503 411 2020-01-25  36.000  
503 411 2020-02-25  72.000  
503 411 2020-02-25  36.000  
503 411 2020-02-25  36.000  
503 411 2020-02-25  543.000 
503 411 2020-02-25  234.000 
503 411 2020-03-25  90.000  
503 411 2020-03-25  324.000 
503 411 2020-03-25  123.000 
503 411 2020-03-25  90.000  
503 411 2020-03-25  84.000  
503 411 2020-03-25  84.000  
503 411 2020-03-25  180.000

If the Code does not have an amount in tblOpenBalance, the sum in formula must be 0. The version of Mysql is 5.5.37

I need this one in one query, but not repeat 12 times for every month in year...:

Closing Balance January 2020: Value From tblOpenBalance + Sum of sales from previous months (where date < January 2020) + sum of sales from January 2020
Closing Balance February 2020: Value From tblOpenBalance + Sum of sales from previous months (where date < February 2020) + sum of sales from February 2020
Closing Balance March 2020: Value From tblOpenBalance + Sum of sales from previous months (where date < March 2020) + sum of sales from March 2020

How come your sample data has codes for both debits and credits and how do we know if the transaction is a debit or credit? — P.Salmon
The column name is CodeDebit and CodeCredit. If i search the balance of 503, i look for it in tblSales. If the code is in column CodeDebit, the value is debit, and same with credit — Axolotl
You have an id in your expected output but no matching id in either of your 2 source tables? Please ensure that when providing sample data an expected output that one can produce the other.. — P.Salmon

ysth ysth · Accepted Answer · 2020-04-21T11:13:25

It sounds like you want the totals only for a single code. If so, something like this:

select year,
    sum(if(transaction_month <= concat(year,'-01'),amount,null)) Jan,
    sum(if(transaction_month <= concat(year,'-02'),amount,null)) Feb,
    sum(if(transaction_month <= concat(year,'-03'),amount,null)) Mar,
    sum(if(transaction_month <= concat(year,'-04'),amount,null)) Apr,
    sum(if(transaction_month <= concat(year,'-05'),amount,null)) May,
    sum(if(transaction_month <= concat(year,'-06'),amount,null)) Jun,
    sum(if(transaction_month <= concat(year,'-07'),amount,null)) Jul,
    sum(if(transaction_month <= concat(year,'-08'),amount,null)) Aug,
    sum(if(transaction_month <= concat(year,'-09'),amount,null)) Sep,
    sum(if(transaction_month <= concat(year,'-10'),amount,null)) Oct,
    sum(if(transaction_month <= concat(year,'-11'),amount,null)) Nov,
    sum(if(transaction_month <= concat(year,'-12'),amount,null)) "Dec"
from (select distinct(year(date)) year from tblSales where CodeDebit='411' or CodeCredit='411') years
join (
    select date_format(date, '%Y-%m') transaction_month, Amount amount
    from tblSales where CodeCredit='411'
    union all
    select date_format(date, '%Y-%m'), -Amount
    from tblSales where CodeDebit='411'
    union all
    select '0000-00', Amount
    from tblOpenBalance where Code='411'
) transactions on transaction_month <= concat(year,'-12')
group by year

Calculate closing balance month by month

1 Answers