SQL - how do I generate rows for each month based on date ranges in existing dataset?

Question

assume I have a dataset:

rowID |  dateStart  |   dateEnd  | Year | Month
121   | 2013-10-03  | 2013-12-03 | NULL | NULL
143   | 2013-12-11  | 2014-03-11 | NULL | NULL
322   | 2014-01-02  | 2014-02-11 | NULL | NULL

And I want sql to generate the following datasource based on the dateStart and the dateEnd. Note the year and month grouping.

rowID |  dateStart  |   dateEnd  | Year | Month
121   | 2013-10-03  | 2013-12-03 | 2013 |   10
121   | 2013-10-03  | 2013-12-03 | 2013 |   11
121   | 2013-10-03  | 2013-12-03 | 2013 |   12
143   | 2013-12-11  | 2014-03-11 | 2013 |   12
143   | 2013-12-11  | 2014-03-11 | 2014 |    1
143   | 2013-12-11  | 2014-03-11 | 2014 |    2
143   | 2013-12-11  | 2014-03-11 | 2014 |    3
322   | 2014-01-02  | 2014-02-11 | 2014 |    1
322   | 2014-01-02  | 2014-02-11 | 2014 |    2

I'm having a hard time wrapping my head around this one. Any ideas?

Answer 1

I find it easiest to approach these problems by creating a list of integers and then using that to increment the dates. Here is an example:

with nums as (
      select 0 as n
      union all
      select n + 1 as n
      from nums
      where n < 11
     )
select rowid, datestart, dateend,
       year(dateadd(month, n.n, datestart)) as yr,
       month(dateadd(month, n.n, datestart)) as mon
from table t join
     nums n
     on dateadd(month, n.n - 1, datestart) <= dateend;

Answer 2

First, create a tabled-valued function that takes the 2 dates and returns the year and month as a table:

create function dbo.YearMonths(@StartDate DateTime, @EndDate DateTime)
returns @YearMonths table
([Year] int,
[Month] int)
as
begin

    set @EndDate = DATEADD(month, 1, @EndDate)
    while (@StartDate < @EndDate)
    begin

    insert into @YearMonths
    select YEAR(@StartDate), MONTH(@StartDate)  

    set @StartDate = DATEADD(month, 1, @StartDate)

    end

return
end

As an example the following:

select *
from dbo.YearMonths('1/1/2014', '5/1/2014')

returns:

Then you would join to it like this to get what you wanted:

select m.*, ym.Year, ym.Month
from myTable m
cross apply dbo.YearMonths(dateStart, dateEnd) ym