Mysql View where a value is split over multiple rows according to date

MySql version: 5.6.47

Assuming I have a table like this:

[from:Datetime][to:Datetime][amount:Decimal(10,2)]
[2020/01/15   ][2020/02/15 ][300                 ]

I want to create a view like this out of it:

[period:char(7)][amount:Decimal(10,2)]
[2020/01       ][150                 ]
[2020/02       ][150                 ]

The from and to dates are split up in the singular months. The amount is multiplied with the amount of days in that particular month over the total amount of days between from and to. From and to could span n amount of month.

Is that even possible or am I wasting my time researching this?

Solution

Assuming that the amount of months in a range is not over 100:

SELECT id,
       datefrom,
       datetill,
       amount,
       monthstart,
       monthfinish,
       amount * (DATEDIFF(LEAST(datetill, monthfinish), GREATEST(datefrom, monthstart)) + 1) / (DATEDIFF(datetill, datefrom) + 1) monthamount
FROM ( SELECT test.*,
              (test.datefrom - INTERVAL DAY(test.datefrom) - 1 DAY) + INTERVAL numbers.num MONTH monthstart,
              LAST_DAY((test.datefrom - INTERVAL DAY(test.datefrom) - 1 DAY) + INTERVAL numbers.num MONTH) monthfinish
       FROM test
       JOIN ( SELECT t1.num*10+t2.num num
              FROM (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1
              JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2
            ) numbers
       HAVING monthstart <= test.datetill
          AND monthfinish >= test.datefrom
     ) subquery
ORDER BY id, monthstart;

fiddle

PS. Don't be surprised if the total sum doesn't match in the last digit.