Generate 1 row for every month between a range of dates

Question

I have the following data, as an example:

Name  | Value       | startdate   | enddate
1     | 10          | 2023-04-01  | 2023-06-30
2     | 99          | 2023-03-01  | 2023-05-01

I need to get the following output:

Name  | Value       | date
1     | 10          | 2023-04
1     | 10          | 2023-05
1     | 10          | 2023-06
2     | 99          | 2023-03
2     | 99          | 2023-04
2     | 99          | 2023-05

Which, in resume, is a new row with the same Value for every month between the date range for every Name column.

Tried some solutions seen here and here, but didn't turned out so great, since it's not the same expected result, and sometimes having the startDate on 2022 and EndDate on 2023 for example breaks some mentioned solutions.

What I made so far:

SELECT
    value,
    DATE_FORMAT(startdate, "%Y-%m") AS startMonth,
    DATE_FORMAT(enddate, "%Y-%m") AS endMonth

FROM
    table

GROUP BY
    --Here's my stuggle

A procedure would help too! As long as it generates that output.

What does `SELECT VERSION();` return on your instance? – Bill Karwin Jun 12 '23 at 18:55 — Bill Karwin, Jun 12 '23 at 18:55
@BillKarwin I'm using the 8.0.33 Version of Mysql. – William Brochensque junior Jun 12 '23 at 18:56 — William Brochensque junior, Jun 12 '23 at 18:56

score 1 · Accepted Answer · answered Jun 12 '23 at 19:03

with recursive dates(name, date) as (
  select name, startdate from mytable where name = 1
  union all
  select dates.name, dates.date + interval 1 month from dates join mytable using (name)
  where dates.date < mytable.enddate
)
select * from dates;

Output:

+------+------------+
| name | date       |
+------+------------+
|    1 | 2023-04-01 |
|    1 | 2023-05-01 |
|    1 | 2023-06-01 |
|    1 | 2023-07-01 |
+------+------------+

Dbfiddle

This is off by one, I'll leave it to you to correct the terminating condition. But it demonstrates the technique of creating a series of successive values using a common table expression.

This is also in the code examples from MySQL: https://dev.mysql.com/blog-archive/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes-part-two-how-to-generate-series/

Works perfectly! Thanks! – William Brochensque junior Jun 12 '23 at 19:09 — William Brochensque junior, Jun 12 '23 at 19:09

Generate 1 row for every month between a range of dates

1 Answers1

Linked