CREATE TABLE STORES (
Store varchar(10), Quarter varchar(10),
Amount int);
INSERT INTO STORES (Store, Quarter, Amount) VALUES ('S1', 'Q1', 200),
('S1', 'Q2', 300),
('S1', 'Q4', 400),
('S2', 'Q1', 500),
('S2', 'Q3', 600),
('S2', 'Q4', 700),
('S3', 'Q1', 800),
('S3', 'Q2', 750),
('S3', 'Q3', 900);
with cte as (select distinct quarter from stores),
cte2 as (select * from cte,
(select distinct store from stores) k)
select cte2.store,cte2.quarter,coalesce(s.amount,0)from cte2
left join
stores s
on s.quarter=cte2.quarter
and s.store=cte2.store
where s.amount is null;
select store,concat('Q',10-sum(cast (right (quarter, 1) as int)))from stores
group by store
Telegram: DB developers channel