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;