mysql rolling sum cumulative serialized

Question

For example I have the bellow table (tb_transaction)

id_trans    date_trans  production_plant    dead_plant  distribution_plant
25          2017-12-31  1000                100             200
26          2018-01-17  150                 0               0
27          2018-02-07  0                   50              100
28          2018-03-07  250                 0               75
29          2018-05-10  500                 50              0

Than I try to make a report table for this Year, like bellow table

month   EarlyStock  production  dead    LivePlant   Distri  EndStock    
January                 150         0       150         0       150 
February                0           50      -50         100     -150    
March                   250         0       250         75      175 
April                   0           0       0           0       0   
May                     500         50      450         0       450 
June                    0           0       0           0       0   
July                    0           0       0           0       0   
August                  0           0       0           0       0   
September               0           0       0           0       0   
October                 0           0       0           0       0   
November                0           0       0           0       0   
December                0           0       0           0       0

Which EarlyStock for January is EndStock December 2017 (assumed EarlyStock for December is 0) which is first data from tb_transaction than EarlyStock for February is EndStock January and so on.

My expected table is Than I try to make a report table for this Year, like bellow table

month   EarlyStock  production  dead    LivePlant   Distri  EndStock    
January     700         150         0       850         0       850 
February    850         0           50      800         100     700 
March       700         250         0       950         75      875 
April       875         0           0       875         0       875 
May         875         500         50      1325        0       1325    
June                    0           0       0           0       0   
July                    0           0       0           0       0   
August                  0           0       0           0       0   
September               0           0       0           0       0   
October                 0           0       0           0       0   
November                0           0       0           0       0   
December                0           0       0           0       0

Formula is:

• LivePlant = EarlyStock + production - dead
• EndStock = LivePlant - Distri

Any Sugestion how I can do it?

Here the db-fiddle for test

Answer 1

It looks like a Rolling Sum problem. It is doable in less verbose manner using Window Functions in MySQL 8.0.2 and onwards. But, since your MySQL version is 5.6, we can emulate this behavior using User-defined Session variables.

The basic gist of this technique is:

• Firstly, in a Derived table, compute the aggregated sum value of various activities like Dead, Distributed etc for a particular Year and Month. In your case, you have data across various year(s), so your approach of doing a grouping on Month alone will not work. You need to Group By on both the year and month. Also, restricting the result-set to Current year alone will not help, as you will need End stock value from the December month of the previous year, in order to get Early stock value for the January month of the next year.
• Now, use this sub-select query's result set, and determine End Stock and Early Stock as per your given definition(s). Conceptually, it is like writing an application code (eg: PHP); we use the previous row's End stock value as Early stock for the current row. At the end, set End stock value to current row's end stock (post computation).
• Now, since you dont want the row corresponding to previous year; I would suggest that you can ignore that row in your Application code. Still if you want to handle it in query only; then you will have to take the complete result-set as a Derived table again, and use Where to filter out row(s) from year(s), other than the current year.

Try the following code (DB Fiddle DEMO):

SELECT t1.year_no,
       t1.month_name,
       @early := @endst                             AS EarlyStock,
       @prod := t1.production                       AS production,
       @dead := t1.dead                             AS dead,
       ( @early + @prod - @dead )                   AS LivePlant,
       @dist := t1.distri                           AS Distri,
       @endst := ( @early + @prod - @dead - @dist ) AS EndStock
FROM   (SELECT Coalesce(Year(trans.date_trans), Year(CURRENT_DATE())) AS year_no,
               Coalesce(Month(trans.date_trans), mon.id_month) AS month_no,
               mon.month_name,
               Coalesce(Sum(trans.production_plant), 0)    AS production,
               Coalesce(Sum(trans.dead_plant), 0)          AS dead,
               Coalesce(Sum(trans.distribution_plant), 0)  AS Distri
        FROM   tb_month AS mon
               LEFT JOIN tb_transaction AS trans
                      ON Month(trans.date_trans) = mon.id_month
        GROUP  BY year_no,
                  month_no,
                  mon.month_name
        ORDER  BY year_no,
                  month_no) AS t1
       CROSS JOIN (SELECT @prod := 0,
                          @dead := 0,
                          @dist := 0,
                          @early := 0,
                          @endst := 0) AS user_init_vars