Full outer join on every row (scaffolding)

Transact-SQL
1

Hello,

I have daily sales data where there are sometimes no sales for certain stores, NULLs on certain days are causing me problems and I would like to see a row with 0. I am wondering how to achieve this, is it possible with a full outer join where I maybe look at the distinct days and combine with the distinct list of shops for example?

Below is a simple example, I would like to see another row for 02/01/26 for Shop 1 with 0 sales:

Date Shop Sales
01/01/2026 Shop 1 2
01/01/2026 Shop 2 3
02/01/2026 Shop 2 1
03/01/2026 Shop 1 3
03/01/2026 Shop 2 4

Thanks!

2

hi

hope this helps

sample data create script 
CREATE TABLE Sales (
    SaleDate DATE,
    Shop VARCHAR(50),
    Sales INT
);

INSERT INTO Sales (SaleDate, Shop, Sales) VALUES
('2026-01-01', 'Shop 1', 2),
('2026-01-01', 'Shop 2', 3);

INSERT INTO Sales (SaleDate, Shop, Sales) VALUES
('2026-01-02', 'Shop 2', 1);

INSERT INTO Sales (SaleDate, Shop, Sales) VALUES
('2026-01-03', 'Shop 1', 3),
('2026-01-03', 'Shop 2', 4);

INSERT INTO Sales (SaleDate, Shop, Sales) VALUES
('2026-01-04', 'Shop 1', NULL),
('2026-01-04', 'Shop 2', 5);

t-sql query

SELECT d.SaleDate,
       s.Shop,
       COALESCE(x.Sales, 0) AS SalesAmount
FROM (SELECT DISTINCT SaleDate FROM Sales) d
CROSS JOIN (SELECT DISTINCT Shop FROM Sales) s
LEFT JOIN Sales x
  ON x.SaleDate = d.SaleDate
 AND x.Shop = s.Shop
ORDER BY d.SaleDate, s.Shop;

output

image
image946×397 16 KB

please provide your comments

did it resolve your issue ?
any mistakes in my code
any suggestions

:winking_face_with_tongue:

3

It worked! Thanks so much for taking the time to help me, I will use this in my work :slight_smile: