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

Question

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?

Does the amount of months included into from-to region has some limit (for example not more than 1 year difference, i.e. 13 months max)? — Akina, May 19 '20 at 09:29
No, there is no limit. It describes how long a project is going to last. So we could assume that no business plans for a 100 years. But it is actually up to the business. Maybe if we get the Illuminati as customers, they have some 500 years world domination projects. — SomeStranger314, May 19 '20 at 10:01

Akina · Accepted Answer · 2020-05-19T11:09:02.517

1

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.

edited May 19 '20 at 11:09

answered May 19 '20 at 09:57

Akina

39,301
5
14
25

That's amazing. Why though the 100 limit? Is that for performance or do I overlook something that limits it? – SomeStranger314 May 19 '20 at 10:02
1

*Why though the 100 limit?* Randomly. *Maybe if we get the Illuminati as customers, they have some 500 years world domination projects.* 500 years = 6000 months. Add 2 digits subqueries (t3 and t4) and edit the expression which calculates the number (1000*t1.num+100*t2.num+10*t3.num+t4.num). – Akina May 19 '20 at 10:07
I start understanding what you're doing it. I think this will perfectly solve my issue. Thank you! – SomeStranger314 May 19 '20 at 10:25
Okay, thanks for the update. I don't really get the explanation. Can you please elaborate? – SomeStranger314 May 19 '20 at 10:55
1

@SomeStranger314 Edited one more time. *Can you please elaborate?* The calculations needs (as one of steps) to obtain the first and the last day of the month. For to obtain the first day I substract the DAY value from the date (obtaining the last day of prev. month) and add 1 day - `(test.datefrom - INTERVAL DAY(test.datefrom) - 1 DAY)`, then add a lot of months. There is no problems there because 1st day of a month is `????-??-01`. But when I calculate the last day there is a problem because day number differs from month to month. Now I use `LAST_DAY` function - it takes this into account. – Akina May 19 '20 at 11:14

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

1 Answers1