From one record to more records that represent mm/yyyy

Question

Let's say that we have this table

Employee	EmploymentStarted	EmploymentEnded
Sara	20210115	20210715
Lora	20210215	20210815
Viki	20210515	20210615

Now what I need is a table that we can see all the employees that we had each month. For example, Sara started on January 15th 2021 and she left the company on July 15th 2021. This means that she has been with us during January, February, March, April, May, June and July.

The result table should look like this:

Month	Year	Employee
January	2021	Sara
February	2021	Sara
February	2021	Lora
March	2021	Sara
March	2021	Lora
April	2021	Sara
April	2021	Lora
May	2021	Sara
May	2021	Lora
May	2021	Viki
June	2021	Sara
June	2021	Lora
June	2021	Viki
July	2021	Sara
July	2021	Lora
August	2021	Lora

How can I get a table like this in SQL?

I tried a group by, but it does not seem to be the right way to do it

Answer 1

It would be interesting to find out in practice how much performance decreases when using recursion. In this case calendarTable contain less about 12 records per year. Most part of query cost is JOIN to Employee (staff) table.

with FromTo as (
  select min(EmploymentStarted) fromDt, eomonth(max(EmploymentEnded)) toDt
  from staff
  )
--,FromTo as(select fromDt=@fromDt,toDt=@toDt)
,rdT as (
  select 1 n,fromDt bM,eomonth(fromDt) eM
        ,fromDt, toDt
  from FromTo
  union all
  select n+1 n,dateadd(day,1,eM) bM,eomonth(dateadd(month,1,bM)) eM
        ,fromDt,toDt
  from rdT where dateadd(month,1,bM)<toDt
)
select month(bM) as 'Month',year(bM) as 'Year',Employee  --* 
from rdT left join staff s on s.EmploymentEnded>=bM and s.EmploymentStarted<=eM

Fiddle