T-SQL Using CTE to aggregate totals for matching and non-matching periods
For your actual query, you could use a FULL JOIN
, which will give you the results from either side also.
But I think there is another solution: you don't need to join separate queries for this, you can just use conditional aggregation
WITH SalesByItem AS (
SELECT
t.Item,
t.Variant
Sales2020 = SUM(CASE WHEN Date BETWEEN '20200701' and '20200705' THEN t.Sales END),
Sales2021 = SUM(CASE WHEN Date BETWEEN '20210701' and '20210705' THEN t.Sales END)
FROM YourTable t
WHERE (Date BETWEEN '20200701' and '20200705'
OR Date BETWEEN '20210701' and '20210705')
GROUP BY
t.Item,
t.Variant
)
SELECT
NewSales = SUM(CASE WHEN Sales2020 IS NULL THEN Sales2021 END),
MatchedSales = SUM(CASE WHEN Sales2020 IS NOT NULL AND Sales2021 IS NOT NULL THEN Sales2021 END),
LostSales = SUM(CASE WHEN Sales2021 IS NULL THEN Sales2020 END)
FROM SalesByItem s;