CODEWITHSUNDEEP
sqlsql-servert-sql
DanF
DanF

Reputation: 3

SELECT row for each month between dates

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.

Upvotes: 0

Views: 154

Answers (2)

yatin parab
yatin parab

Reputation: 174

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

Upvotes: 0

dean
dean

Reputation: 10098

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]

Upvotes: 1

Related Questions