CODEWITHSUNDEEP
mysql
Axolotl
Axolotl

Reputation: 35

Calculate closing balance month by month

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:

  1. Value from tblOpenBalance
  2. 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)
  3. 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...:

Upvotes: 2

Views: 696

Answers (1)

ysth
ysth

Reputation: 98388

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

Upvotes: 3

Related Questions