CODEWITHSUNDEEP
mysqlsql
Simon Breton
Simon Breton

Reputation: 2876

divide a monthly cost by the number of month left in the year and split it over multiple rows

I don't know where to start to build this query.

I have the following simple rows :

event, date,        value, months left
foo    01/06/2018   700    7
bar    01/08/2018   50     5

I want the following output:

event, date,        value, months left
foo    01/06/2018   100    7
foo    01/07/2018   100    7
foo    01/08/2018   100    7
foo    01/09/2018   100    7
foo    01/10/2018   100    7
foo    01/11/2018   100    7
foo    01/12/2018   100    7
bar    01/08/2018   10     5
bar    01/09/2018   10     5
bar    01/10/2018   10     5
bar    01/11/2018   10     5
bar    01/12/2018   10     5

How can I achieve this? I really have no idea. I found some similar questions but didn't really understand it.

Thanks.

Upvotes: 0

Views: 279

Answers (2)

Juan
Juan

Reputation: 5589

The way I have found to do this requires an additional table to hold the forthcoming number of months for each month's end value. This assumes that it will be applied for a number of installments for example where total number of forthcoming months is relatively small, 12 months for example.

This setup is just for the sample data:

create table months_pool(
  month integer,
  mleft integer
);

month is the number of moths left as set in the event table. mleft goes from 0 to month - 1. It will be used to calculate the date for each record of the resulting table.

insert into months_pool 
values
(7,0),(7,1),(7,2),(7,3),(7,4),(7,5),(7,6),
(5,0),(5,1),(5,2),(5,3),(5,4);

The query for the result:

select 
e.event, ADDDATE(date, interval mp.mleft month) dte, 
e.value/e.mleft val, 
e.mleft ml
from event e inner join months_pool mp on (e.mleft = mp.month)
order by e.event , ADDDATE(date, interval (mp.mleft) month);

EDIT I

I see @MartinPierce posted an answer in these lines. All the same I will leave it because of the use of ADDDATE() funcion.

Upvotes: 1

Marlin Pierce
Marlin Pierce

Reputation: 10079

I was able to do this, by creating a table with the month numbers 1 to 12.

> select * from events;
+-------+------------+--------+
| event | event_date | value  |
+-------+------------+--------+
| foo   | 2018-06-01 | 700.00 |
| bar   | 2018-08-01 |  50.00 |
+-------+------------+--------+

> select * from months;
+--------------+
| month_number |
+--------------+
|            1 |
|            2 |
|            3 |
|            4 |
|            5 |
|            6 |
|            7 |
|            8 |
|            9 |
|           10 |
|           11 |
|           12 |
+--------------+

> select event, concat(2018, '-', LPAD(month_number, 2, '00'), '-01') as curr_date, value / (13 - month(event_date)) as value, (13 - month_number) as months_remain from events, months where month_number >= month(event_date) order by event, months_remain desc;
+-------+------------+------------+---------------+
| event | curr_date  | value      | months_remain |
+-------+------------+------------+---------------+
| bar   | 2018-08-01 |  10.000000 |             5 |
| bar   | 2018-09-01 |  10.000000 |             4 |
| bar   | 2018-10-01 |  10.000000 |             3 |
| bar   | 2018-11-01 |  10.000000 |             2 |
| bar   | 2018-12-01 |  10.000000 |             1 |
| foo   | 2018-06-01 | 100.000000 |             7 |
| foo   | 2018-07-01 | 100.000000 |             6 |
| foo   | 2018-08-01 | 100.000000 |             5 |
| foo   | 2018-09-01 | 100.000000 |             4 |
| foo   | 2018-10-01 | 100.000000 |             3 |
| foo   | 2018-11-01 | 100.000000 |             2 |
| foo   | 2018-12-01 | 100.000000 |             1 |
+-------+------------+------------+---------------+

Upvotes: 2

Related Questions