T-SQL Using CTE to aggregate totals for matching and non-matching periods-CodePudding

Matched Sales are provided by the join, It's getting the unmatched that is eluding me.

CTE
With PriorSalesCTE
    (
    Item
Variant,
    Sum(sales)
    Date between 7/1/2020 and 7/5/2020
),
    CurrentSalesCTE
(
    Item
Variant,
    Sum(sales)
    Date between 7/1/2021 and 7/5/2021
)
Select 
SUM(cs.Sales) ‘MatchedSales’
FROM PriorSalesCTE ps join CurrentSalesCTE ps
    ON cs.Item = ps.Item
    And cs.Variant = ps.Variant

Now I need the empty spaces on both sides I need the sales for items sold in 2020 but not sold in 2021 – Lost Sales Conversely, sales for 2021 that did not sell in 2020 – New Sales.

I tried adding these in the CTE as separate sections of the CTE, but the join doesn’t give me what I need.

Any suggestions? Is the CTE simply preventing me for getting everything and maybe add a UNION ALL query to get the unmatched values?

CodePudding user response：

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;