How to get a count between to dates and write the count of rows into a table with T-SQL?

Question

My Language is T-SQL and I am working with MS SQLServer 2008.

Well, I have a table with a lot of data with information concerning employees. Every employee has a "startdate" (the time when he startet to work for the company) and and "enddate" (the time when he quit the job). I would like to write into a table the same count of rows as the employee worked for the company in month. For example:

My basic table:

---

Employee Number | StartDate | EndDate 4711 20150101 20150523

---

This example shows that the employee worked for the company for 5 Month. So I want to insert in the new table 5 rows with the following information:

New Table:

---

  Employee Number | StartDate | EndDate
  row1: 4711               20150101   20150523

  row2: 4711               20150201   20150523

  row3: 4711               20150301   20150523

  row4: 4711               20150401   20150523

  row5: 4711               20150501   20150523

---

I tried this to get the number of month between the dates. I guess I need to work with a cursor or something like that.

    declare @start DATE = '2011-05-01'
declare @end DATE = '2011-08-01'

;with months (date)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(month,1,date)
    from months
    where DATEADD(month,1,date)<=@end
)
select Datename(month,date) from months

Hope you got the idea, I tried to be as specific as I can.

Answer 1

I think that you were on right path.

declare @start DATE = (select min(startdate) from dbo.employee)
declare @end DATE = cast(sysdatetime() as date)

set @start = DATEADD(day, - datepart(day, @start) + 1, @start)

;with months (date)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(month,1,date)
    from months
    where DATEADD(month,1,date)<=@end
)
select employee.EmployeeNumber, Year = datepart(year, date), Month = DATENAME(month, date), employee.StartDate, employee.EndDate
from months
inner join dbo.employee on month.date >= employee.startdate and (month.date <= employee.enddate or employee.enddate is null)