Adding rows with quarters - filling gaps

Question

How can I add the missing quarters between two quarters? I have a table dbo.calendars with all quarters since 2000. And another table with the ids and the quarters. Example of dbo.calendars: 200001,200002,200003,200004,200101,....

I want to fill the gap for the id P206M02117. The id P206M02118 is right because there are no gaps between quarters.

My wrong output
id           qtr
P206M02117  200601
P206M02117  200604
P206M02118  200604
P206M02118  200701
P206M02118  200702

Output I want
id           qtr
P206M02117  200601
P206M02117  200602
P206M02117  200603
P206M02117  200604
P206M02118  200701
P206M02118  200702

Thanks in advance.

Answer 1

[Edited to help with the modified question]

It's not clear how id is populated when the row for a quarter is not present; the query below assumes it's populated with the max(id) in those cases.

You can do:

select x.id, c.qtr
from calendars c
left join (
  select id, min(qtr) as minq, max(qtr) as maxq from t group by id
) x on c.qtr between x.minq and x.maxq
where x.id is not null

Result:

 id           qtr    
 ------------ ------ 
 PM206M02117  200601 
 PM206M02117  200602 
 PM206M02117  200603 
 PM206M02117  200604 
 PM206M02118  200604 
 PM206M02118  200701 
 PM206M02118  200702 

See running example at db<>fiddle.