1

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 ( desired output )

    +----------+---------------+------------+------------+------------+------------+-------------+-------------+-------------+-------------+------------+------------+
    |   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 our value from January
  • + ( method : add PK_cor 1 ) the value 150
  • * 1.02 ( the @default_percentage changed from 1.03 to 1.02 )

In other cases it remains the same -1 month * 1.03

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

  • A database is more of a data store than a computation engine. – Rick James Dec 5 '17 at 23:26
  • Well that's that I expected, but how far can I go SQL wise with this one? – Kavvson Empcraft Dec 6 '17 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.