CODEWITHSUNDEEP
mysqlsql
Aaron
Aaron

Reputation: 1737

Show months for time range in select statement

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?

Upvotes: 0

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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.

Upvotes: 1

Luuk
Luuk

Reputation: 14958

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.

Upvotes: 1

Related Questions