CODEWITHSUNDEEP
sql-servert-sqldatesql-server-2014recursive-query
ausmod
ausmod

Reputation: 71

SQL Server Fill Month Gaps in Groups

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.

Upvotes: 0

Views: 233

Answers (1)

GMB
GMB

Reputation: 222462

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

Upvotes: 1

Related Questions