How to get data for missing months

Question

Hi,

Below is my DDL

CREATE TABLE #Temp
(
Id INT,
ReportMonth int,
ReportQuarter int,
PaymentAmount MONEY
)

INSERT INTO #Temp
VALUES (123,1,1,25), (123,3,1,50),(123,4,2,50),(123,5,2,50),(135,1,1,50),(135,4,2,50)

SELECT * FROM #Temp

DROP TABLE #Temp

Below are the images of Input & Output i am trying to achieve, Can someone please help me with this

Answer 1

Try this query:

;
with Y
as
(
    select m 
    from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) as Y(m)
),
X as
(
    select Id, MAX(ReportMonth) as mm
    from #Temp
    group by Id
)
select X.Id, 
    Y.m as ReportMonth, 
    (Y.m - 1) / 3 + 1 as ReportQuarter, 
    isnull(t.PaymentAmount, $0) as PaymentAmount, 
    sum(t.PaymentAmount) over (partition by X.Id order by Y.m rows between unbounded preceding and current row) as CummulativeAmount
from Y
cross apply X
left join #Temp as t on t.Id = X.Id and t.ReportMonth = Y.m
where Y.m <= X.mm
order by Id, ReportMonth