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