SQL Server Fill Month Gaps in Groups

Question

I have a table for example like below:

declare @test table(Aid int, Bid int, CheckMonth date, Avalue decimal(18,2))

insert into @test (Aid, Bid, CheckMonth, Avalue)
values (1, 4, '2014-07-05', 123.00)
      ,(1, 4, '2014-08-01', 467.00)
      ,(1, 4, '2014-11-03', 876.00)
      ,(1, 4, '2014-12-01', 876.00)
      ,(2, 6, '2016-01-02', 23.00)
      ,(2, 6, '2016-03-14', 56.00)
      ,(2, 6, '2016-04-17', 98.00)
      ,(2, 6, '2016-07-01', 90.00)

I wish to fill the gaps in months (in CheckMonth column above) with 0.00 values (in Avalue column). Data is grouped by Aid and Bid columns.

The result should look like below:

Aid   Bid    CheckMonth     Avalue
1     4      '2014-07-05'   123.00
1     4      '2014-08-01'   467.00
1     4      '2014-09-01'   0.00    -->inserted
1     4      '2014-10-01'   0.00    -->inserted
1     4      '2014-11-03'   876.00
1     4      '2014-12-01'   876.00
2     6      '2016-01-02'   23.00
2     6      '2016-02-01'   0.00    -->inserted
2     6      '2016-03-14'   56.00
2     6      '2016-04-17'   98.00
2     6      '2016-05-01'   0.00    -->inserted
2     6      '2016-06-01'   0.00    -->inserted
2     6      '2016-07-01'   90.00

Any help is appreciated. Thanks.

@DaleK, I tried creating row number and count for the groups but it works only for one gap. It does not work if gap is over several months — ausmod, May 07 '20 at 22:12
@DaleK,I had this solution initially With FinalData as (select Aid,Bid,CheckMonth,Avalue ,row_number() over(partition by Aid, Bid order by Aid, Bid, CheckMonth) as rowNum ,count(*) over(partition by Aid, Bid) as cnt from test ) select Aid,Bid,CheckMonth,Avalue from test union select t1.Aid,t1.Bid,dateadd(month, 1, t1.CheckMonth) CheckMonth,0.00 from FinalData t1 left join FinalData t2 on t1.rowNum + 1 = t2.rowNum and datediff(month, t1.CheckMonth, t2.CheckMonth) in(0,1) and t1.Aid = t2.Aid and t1.Bid = t2.Bid where t2.rowNum is null and t1.rowNum <> t1.cnt — ausmod, May 08 '20 at 01:19
@DaleK, sorry for the formatting. I could not format the above code in the comment — ausmod, May 08 '20 at 01:25
Thats because we don't want it in a comment ") [edit] it into your question please. — Dale K, May 08 '20 at 01:25

score 1 · Accepted Answer · answered May 07 '20 at 22:13

1

One option uses a recursive query to generate the month starts for each (aid, bid) tuple; you can then left join the generated resultset with the original table:

with cte as (
    select 
        aid, 
        bid, 
        datefromparts(year(min(checkMonth)), month(min(checkMonth)), 1) dt, 
        datefromparts(year(max(checkMonth)), month(max(checkMonth)), 1) maxDt 
        from @test
        group by aid, bid
    union all
    select 
        aid, 
        bid, 
        dateadd(month, 1, dt),
        maxDt
    from cte
    where dt < maxDt
)
select c.aid, c.bid, coalesce(t.checkMonth, c.dt) checkMonth, coalesce(t.avalue, 0) avalue 
from cte c
left join @test t 
    on  t.aid = c.aid
    and t.bid = c.bid
    and t.checkMonth >= c.dt
    and t.checkMonth <  dateadd(month, 1, c.dt)
order by c.aid, c.bid, c.dt

Demo on DB Fiddle:

aid | bid | checkMonth | avalue
--: | --: | :--------- | :-----
  1 |   4 | 2014-07-05 | 123.00
  1 |   4 | 2014-08-01 | 467.00
  1 |   4 | 2014-09-01 | 0.00  
  1 |   4 | 2014-10-01 | 0.00  
  1 |   4 | 2014-11-03 | 876.00
  1 |   4 | 2014-12-01 | 876.00
  2 |   6 | 2016-01-02 | 23.00 
  2 |   6 | 2016-02-01 | 0.00  
  2 |   6 | 2016-03-14 | 56.00 
  2 |   6 | 2016-04-17 | 98.00 
  2 |   6 | 2016-05-01 | 0.00  
  2 |   6 | 2016-06-01 | 0.00  
  2 |   6 | 2016-07-01 | 90.00

answered May 07 '20 at 22:13

GMB

216,147
25
84
135

Thanks for this, but I get the following error when running in live data: Msg 530, Level 16, State 1, Line 1 The statement terminated. The maximum recursion 100 has been exhausted before statement completion. – ausmod May 07 '20 at 22:51
@ausmod: you have data that spreads over more than 100 months. You can just add `option(maxrecursion 0)` at the very end of the query to avoid that error. – GMB May 07 '20 at 22:56
Thanks again. I tried to add to the option statement to the last line of my view, which is just before your order by statement above. But I get Incorrect syntax near the keyword 'option'. error. Do you know where it has to be added to above? – ausmod May 07 '20 at 22:59
@ausmod: ah, if you use that query to create a view, that's different. In that case, you need to put the option in each query that addresses the view, not in the definition of the view itself. Say you view is `myview`, then you do: `select * from myview option(maxrecursion 0)`. – GMB May 07 '20 at 23:02
I think we have to add this option when run the view including the SQL statements above. Correct? Is there a way to include it in the view? Thanks – ausmod May 07 '20 at 23:08
@ausmod: I don't think that it is possible to put the `option` in the view definition. See [this SO post](https://stackoverflow.com/questions/27172801/ms-sql-server-how-to-create-a-view-from-a-cte) for your reference. – GMB May 07 '20 at 23:12

SQL Server Fill Month Gaps in Groups

1 Answers1