I recently came up with this and I am not sure if it is even possible to do in Mysql. The idea is to create a forecast/prediction of expenses based on last 3 months also adding some conditions for the values. Currently I created two working queries, but I am unable to transform my vision into the query for the prediction table. The questions are : Is it possible to achieve this? And is this possible to make it one query. Any hints will be appreciated. I am willing to use this in my PHP script, but I want to reduce the query amount and preferably have it later translated into a SP.

The query logic.

• Getting the average expense cost of last 3 month
• Display Verified sales per month
• Forecast expenses for next year ( and include corrections )

Display Verified sales per month output

+-----+-----+-----+-----+-----+--------+--------+----------+----------+----------+----------+------+ | jan | feb | mar | apr | may |  june  |  july  |   augu   |   sept   |   oct    |   nov    | dece | +-----+-----+-----+-----+-----+--------+--------+----------+----------+----------+----------+------+ |   0 |   0 |   0 |   0 |   0 | 387.71 | 387.71 | 71026.92 | 43914.10 | 61683.26 | 20898.04 |    0 | +-----+-----+-----+-----+-----+--------+--------+----------+----------+----------+----------+------+ 

Forecast logic

We first get the average expense cost of last 3 month, and each forecast month should be increased by a given percentage

set @default_percentage = 1.03; 

The percentage may change for a given month based on the table forecast_correction

+--------+---------+-------+------+-------+ | PK_cor | method  | month | year | value | +--------+---------+-------+------+-------+ |      1 | add     |     2 | 2018 |   150 | |      2 | percent |     2 | 2018 |     2 | +--------+---------+-------+------+-------+ 

We forecast the expenses for next year,

• 41290.65 : the average expense cost of last 3 month
• 1.03 : @default_percentage = 1.03 variable

Prediction table – basic

    +----------+---------------+------------+------------+------------+------------+-------------+-------------+-------------+-------------+------------+------------+     |   jan    |      feb      |    mar     |    apr     |    may     |    june    |    july     |    augu     |    sept     |     oct     |    nov     |    dece    |     +----------+---------------+------------+------------+------------+------------+-------------+-------------+-------------+-------------+------------+------------+     | 41290.65 | 41290.65*1.03 | feb * 1.03 | mar * 1.03 | apr * 1.03 | may * 1.03 | june * 1.03 | july * 1.03 | augu * 1.03 | sept * 1.03 | oct * 1.03 | nov * 1.03 |     +----------+---------------+------------+------------+------------+------------+-------------+-------------+-------------+-------------+------------+------------+ 

But as we have some entries in the forecast_correction table

The calculations should be made as following for February ( since we have month : 2 in the table )

• 41290.65 – that’s out value from January
• + ( method : add PK_cor 1 ) the value 150
• * 1.02 ( the @default_percentage changed from 1.03 to 1.02 )

MCVE : https://www.db-fiddle.com/f/5FtV24czfAfUiE1dj5EeYt/23