Expanding Months Between Two Dates

Question

I am trying to find a way in SQL to list all of the months between two dates, per user. The table would have:

RecordNumber(PK), StartDate, EndDate, Type.

I found another stackoverflow page for All Months between two dates, but it didn't work for my usecase, as I think it would only work for a single individual.

The idea would be that there is another field created that would result in each month and year between the start and end dates.

So for a example table:

Record   Startdate    End Date     Type    
1234   | 01-01-2019 | 04-01-2019 | TypeA 
5678   | 03-01-2019 | 04-01-2019 | TypeB

The result would be:

Record   Startdate    End Date     Type    NewField
1234   | 01-01-2019 | 04-01-2019 | TypeA | January2019
1234   | 01-01-2019 | 04-01-2019 | TypeA | February2019
1234   | 01-01-2019 | 04-01-2019 | TypeA | March2019
1234   | 01-01-2019 | 04-01-2019 | TypeA | April2019
5678   | 03-01-2019 | 04-01-2019 | TypeB | March2019
5678   | 03-01-2019 | 04-01-2019 | TypeB | April2019

Answer 1

You can use a recursive CTE:

with cte as (
      select Record, Startdate, EndDate, Type, startdate as dte
      from t
      union all
      select Record, Startdate, EndDate, Type, dateadd(month, 1, dte) as dte
      from cte
      where dte < enddate
     )
select Record, Startdate, EndDate, Type, datename(month, dte) + datename(year, dte)
from cte
order by record, dte;

Here is a db<>fiddle.

If you might have more than 100 months for a given record, then include option (maxrecursion 0).