Show months for time range in select statement

Question

I've got the following datastructure:

ID,Name,Start,End
1,Aaron,01-01-2020,31-12-2020
2,Example,01-01-2020,31-6-2020

Start and End represents a date.

The outcome of my select should look like the following, supposing, I start with the data above:

ID,Name,Month
1,Aaron,January
1,Aaron,February
1,Aaron,March
1,Aaron,April...
1,Aaron,December


2,Example,January...
2,Example,June.

So what I actually want is to get all months inside each time range. Is this possible with one select? Or can I try a different approach?

This depends on the version of MySQL used, in 8.0 you can use `WITH`, which does not exist in previous versions. — Luuk, Jun 24 '20 at 06:11
Or you could get MySQL to get the minimal data and let your application handle the consequences of expanding the format. — danblack, Jun 24 '20 at 06:11
@danblack tried that already but I'm using PowerBI which isnt really intuitive when it comes to data formatting / expanding. — Aaron, Jun 24 '20 at 06:12
With previous versions (before 8.0) see: https://stackoverflow.com/questions/9511409/creating-a-list-of-month-names-between-two-dates-in-mysql — Luuk, Jun 24 '20 at 06:13
@Luuk could you maybe provide me a solution for my question using WITH? I cant really find what I need — Aaron, Jun 24 '20 at 06:18

Luuk · Answer 1 · 2020-06-24T11:11:35.080

with recursive months(x,m,mn) as (
  select '2019-01-01' as x, month('2019-01-01')as m, monthname('2019-01-01') as mn
  union all
  select DATE_ADD(x,INTERVAL 1 MONTH), MONTH(DATE_ADD(x,INTERVAL 1 MONTH)), MONTHNAME(DATE_ADD(x,INTERVAL 1 MONTH))
  from months
  where x<'2023-12-31'
)
select d.ID, d.Name, m.mn 
from datastructure d
inner join months m on x between d.start and d.end;

In above statement, the currently fixed values 2019-01-01 and 2023-12-31 should be changed to the needs of the user....

With WITH a temporary table is created which can be used in the next select statement.

score 1 · Accepted Answer · answered Jun 24 '20 at 12:18

1

I recommend a recursive CTE but directly on your data. Something like this:

with recursive cte as (
      select id, name, start, end
      from t
      union all
      select id, name, start + interval 1 month, end
      from cte
      where start < end
     )
select id, name, monthname(start) as month
from cte;

There is no need to generate a list of months and dates and then join back to the original data.

answered Jun 24 '20 at 12:18

Gordon Linoff

1,242,037
58
646
786

Thanks for your solution. Unfortunately i had to generate a table of dates for PowerBI since it doesnt work with CTE or the other solution. – Aaron Jun 24 '20 at 12:24

Show months for time range in select statement

2 Answers2