Sql - How Do I Generate Rows For Each Month Based On Date Ranges In Existing Dataset?

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-

Solution 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 (
      select0as n
      unionallselect n +1as 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
fromtable t join
     nums n
     on dateadd(month, n.n -1, datestart) <= dateend;

Solution 2:

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

createfunction dbo.YearMonths(@StartDate DateTime, @EndDate DateTime)
returns@YearMonthstable
([Year] int,
[Month] int)
asbeginset@EndDate= DATEADD(month, 1, @EndDate)
    while (@StartDate<@EndDate)
    begininsertinto@YearMonthsselectYEAR(@StartDate), MONTH(@StartDate)  

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

    endreturnend

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

Solution 3:

Try this:

declare@monthstable(mth int)
insertinto@monthsvalues(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)

declare@calendartable(yr int,mth int)
insertinto@calendarselectdistinctyear(datestart),mth
from tbl crossjoin@monthsunionselectdistinctyear(dateend),mth
from tbl crossjoin@monthsselect t.rowID, t.datestart, t.dateend, y.yr [Year], y.mth [Month] 
from
yourtable t
innerjoin@calendar y onyear(datestart) = yr oryear(dateend) = yr
where 
(mth >=month(datestart) and mth <=month(dateend) andyear(datestart) =year(dateend))
or 
(year(datestart) <year(dateend)) 
 and 
 (year(datestart) = yr and mth >=month(datestart) --All months of start yearor 
 (year(dateend) = yr and mth <=month(dateend))) -- All months of end yearorderby t.rowID, [Year],[Month]

We create a 'Calendar table' which lists all the month and year combinations present in the source table. Then, we join the source table to the calendar table based on the year, and filter as required.