Return rows with values that don't exist in another table

Question

I have a query for showing companies that have submitted at least one report but have not reported for all of the prior eight quarters. The following is the query:

select distinct ct.CompanyId, c.companyName, 
'Reported Quarters' = STUFF(
                                (
                                    select distinct ', ' + CAST(QuarterId as varchar(max))
                                    from CompanyTransaction as b
                                    where b.CompanyId = ct.CompanyId
                                    FOR XML PATH('') 
                                ),1,1,''
                            )
                            
from CompanyTransaction as ct
left join ( select CompanyId, count(distinct QuarterId) as cnt
            from CompanyTransaction
            group by CompanyId
            having count(distinct QuarterId) = 8
          ) as ct2 on ct.CompanyId = ct2.CompanyId
join Company as c on ct.CompanyId = c.CompanyId
where ct2.CompanyId is null
order by ct.CompanyId

I did a self left join on CompanyTransaction table to retrieve companies that do not have total count of 8 distinct QuarterIds. The result of the query shows the Company Id's, Company Names, and Reported Quarters (the quarters each company has submitted).

CompanyId   CompanyName                 Reported Quarters
1           Cabbage Corp                20191, 20192, 20193
2           Future Industries           20191, 20192, 20194, 20201
3           Republic City               20191, 20192, 20193
4           Keum Enterprises            20191, 20203
5           Varrick Global Industries   20191, 20192, 20193, 20194, 20201, 20202, 20204

What I would like to show, and unsure of implementing, are the quarters that a company that has not reported for:

CompanyId   CompanyName                 Missing Quarters
1           Cabbage Corp                20194, 20201, 20202, 20203, 20204
2           Future Industries           20193, 20202, 20203, 20204
3           Republic City               20194, 20201, 20202, 20203, 20204
4           Keum Enterprises            20192, 20193, 20194, 20201, 20202, 20204
5           Varrick Global Industries   20203

Any help and/or suggestions are greatly appreciated!

Answer 1

What I would like to show, and unsure of implementing, are the quarters that a company that has not reported for:

Let me assume that all tables and all quarters are in CompanyTransaction. Then the idea is to generate rows for all companies and quarters and filter out the ones that are not present.

The following will use string_agg(). You seem to know how to convert to stuff() if necessary:

select c.companyid, c.companyname,
       string_agg(q.quarterid, ', ') within group (order by q.quarterid)
from (select distinct companyid, companyname
      from CompanyTransaction
     ) c cross join
     (select distinct quarterid
      from CompanyTransaction
     ) q left join
     CompanyTransaction ct
     on ct.companyid = c.companyid and
        ct.quarterid = q.quarterid
where ct.companyid is null
group by c.companyid, c.companyname;

Actually, the old-style aggregation method is not that bad:

select c.companyid, c.companyname,
       stuff( (select concat(', ', q.quarterid)
               from quarters q left join
                    CompanyTransaction ct
                    on ct.quarterid = q.quarterid and
                       ct.companyid = c.companyid
               where ct.quarterid is null
               order by q.quarterid
               for xml path ('')
              ), 1, 2, ''
            ) as missing_quarterids
from (select distinct companyid, companyname
      from CompanyTransaction
     ) c;