SELECT row for each month between dates

Question

I'm trying to work out how to select a row for each month between a period. The period will have a start date of the earliest month within the table. So in the example below the start period will be 2016-01-01, but the end period will be user defined, lets say 2017-02-01. My table:

rowID | Month      | someDate    | SomeOtherDate |  Number
1     | 2016-01-01 | 2018-01-01  | 2018-01-01    | 0 
2     | 2016-07-01 | 2019-03-01  | 2019-02-01    | 1

The result I'm looking for is this:

Month      | someDate    | SomeOtherDate |  Number
2016-01-01 | 2018-01-01  | 2018-01-01    | 0 
2016-02-01 | 2018-01-01  | 2018-01-01    | 0
2016-03-01 | 2018-01-01  | 2018-01-01    | 0
2016-04-01 | 2018-01-01  | 2018-01-01    | 0
2016-05-01 | 2018-01-01  | 2018-01-01    | 0
2016-05-01 | 2018-01-01  | 2018-01-01    | 0
2016-07-01 | 2019-03-01  | 2019-02-01    | 1
2016-08-01 | 2019-03-01  | 2019-02-01    | 1
2016-09-01 | 2019-03-01  | 2019-02-01    | 1
2016-10-01 | 2019-03-01  | 2019-02-01    | 1
2016-11-01 | 2019-03-01  | 2019-02-01    | 1
2016-12-01 | 2019-03-01  | 2019-02-01    | 1
2017-01-01 | 2019-03-01  | 2019-02-01    | 1
2017-02-01 | 2019-03-01  | 2019-02-01    | 1

Essentially I need to duplicate the row until it finds another row or meets the period end date, if another row is found then duplicate that row until the end period is met, while incrementing the Month for each row. Hope that makes sense.

Any help would be greatly appreciated.

You need another calendar table, filled by all dates. You can fill it before your query or 'on the fly' with some CTE's. For example http://stackoverflow.com/questions/5635594/how-to-create-a-calender-table-for-100-years-in-sql — xdd, Jul 15 '16 at 12:46

score 1 · Accepted Answer · answered Jul 15 '16 at 13:09

You really need the Numbers table for this kind of problems. I'll simulate it here with table variable, but pls serch around for an exmple how to create a proper one in the database.

Here's the solution:

declare @num table(n int)
insert @num values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)

declare @t table (rowID int, Month date, someDate date, SomeOtherDate date, Number int)
insert @t values
(1     , '2016-01-01' , '2018-01-01'  , '2018-01-01'    , 0 ),
(2     , '2016-07-01' , '2019-03-01'  , '2019-02-01'    , 1 )

declare @end_date date = '20170201'
set @end_date = dateadd(month, 1, @end_date);

;with x as (
select *, datediff(month, [month], isnull(lead([month]) over(order by rowid), @end_date) ) dd 
from @t
)
select dateadd(month, n, [month]) Month, someDate, SomeOtherDate, Number
from x
join @num on dd > n
order by [month]

Thanks @dean, created a number table as suggested, and tweaked the query slightly to include a start date, which is working great. Thanks again for your help. — DanF, Jul 18 '16 at 08:38

score 0 · Answer 2 · answered Jul 15 '16 at 13:28

Try This it will give you missing data, then try to inert in your table

create table #temp(rowID int,Month datetime,someDate datetime,SomeOtherDate datetime,Number int)

insert into #temp values(1,'2016-01-01','2018-01-01','2018-01-01',0)
insert into #temp values(2,'2016-07-01','2018-03-01','2019-02-01',1)
insert into #temp values(3,'2016-09-01','2018-03-01','2019-02-01',1)

declare @counter int,@Current int
select @counter=count(1) from #temp
set  @Current=1

create table #MissingData(rowID int,Month datetime) 

while(@counter>@Current)
begin
    DECLARE @start datetime,
    @end datetime
    select @start=Month from #temp where rowID=@Current
    select @end=Month from #temp where rowID=@Current+1
    if(@end is not null)
    begin
       ;WITH IntervalDates (datetime)
       AS
       (
          SELECT @start
          UNION ALL
          SELECT DATEADD(MONTH, 1, datetime)
          FROM IntervalDates
          WHERE DATEADD(MONTH, 1, datetime)<=@end
       )
       insert into #MissingData
       SELECT @Current,convert(datetime,Convert(varchar,YEAR(datetime))+'-'+Convert(varchar,MONTH(datetime))+'-01')
       FROM IntervalDates 
       where convert(datetime,Convert(varchar,YEAR(datetime))+'-'+Convert(varchar,MONTH(datetime))+'-01') not in (@start,@end)
       order by YEAR(datetime),MONTH(datetime) 
    end
    select @Current=@Current+1
end

select * from select * from #MissingData

SELECT row for each month between dates

2 Answers2