CODEWITHSUNDEEP
sqlsql-servert-sqlcommon-table-expression
nicktight
nicktight

Reputation: 41

Joining multiple CTEs

I am working on a database of a large retail store. I have to query data from multiple tables to get numbers such as revenue, raw proceeds and compare different time periods. Most of it is quite easy but I was struggling to work out a way of joining multiple CTEs. I made a fiddle so you know what I am talking about. I simplified the structure a lot and left out quite a few columns in the subqueries because they do not matter in this case.

As you can see every row in every table has country and brand in it. The final query has to be grouped by those. What I first tried was to FULL JOIN all the tables, but that didn't work in some cases as you can see here: SQLfiddle #1. Note the two last rows which did not group correctly.

Select Coalesce(incoming.country, revenue.country, revcompare.country,
  openord.country) As country,
  Coalesce(incoming.brand, revenue.brand, revcompare.brand,
  openord.brand) As brand,
  incoming.OrdersNet,
    openord.OpenOrdersNet,
    revenue.Revenue,
    revenue.RawProceeds,
    revcompare.RevenueCompare,
    revcompare.RawProceedsCompare
From incoming
  Full Join openord On openord.country = incoming.country And
    openord.brand = incoming.brand
  Full Join revenue On revenue.country = incoming.country And
    revenue.brand = incoming.brand
  Full Join revcompare On revcompare.country = incoming.country And
    revcompare.brand = incoming.brand
Group By incoming.OrdersNet,
    openord.OpenOrdersNet,
    revenue.Revenue,
    revenue.RawProceeds,
    revcompare.RevenueCompare,
    revcompare.RawProceedsCompare,
  incoming.country,
  revenue.country,
  openord.country,
  revcompare.country,
  incoming.brand,
  revenue.brand,
  revcompare.brand,
  openord.brand
Order By country,
  brand

I then rewrote the query keeping all the CTEs. I added another CTE (basis) which UNIONs all the possible country and brand combinations and left joined on that one. Now it works fine (check it out here -> SQLfiddle #2) but it just seems so complicated. Isn't there an easier way to achieve this? The only thing I probably won't be able to change are the CTEs as in real life they are way more complex.

WITH basis AS (
  SELECT Country, Brand FROM incoming
  UNION
  SELECT Country, Brand FROM openord
  UNION
  SELECT Country, Brand FROM revenue
  UNION
  SELECT Country, Brand FROM revcompare
)

SELECT 
    basis.Country,
    basis.Brand,
    incoming.OrdersNet,
    openord.OpenOrdersNet,
    revenue.Revenue,
    revenue.RawProceeds,
    revcompare.RevenueCompare,
    revcompare.RawProceedsCompare
FROM basis
LEFT JOIN incoming   On incoming.Country = basis.Country AND incoming.Brand = basis.Brand
LEFT JOIN openord        On openord.Country = basis.Country AND openord.Brand = basis.Brand
LEFT JOIN revenue      On revenue.Country = basis.Country AND revenue.Brand = basis.Brand
LEFT JOIN revcompare On revcompare.Country = basis.Country AND revcompare.Brand = basis.Brand

Thank you all for your help!

Upvotes: 1

Views: 1588

Answers (1)

Parfait
Parfait

Reputation: 107587

Since you only work with two tables, orders and rev, consider conditional aggregation by moving WHERE conditions to CASE logic for single aggregate query. Also, consider only one CTE for all possible country/brand pairs for LEFT JOIN on the two tables.

WITH cb AS (
  SELECT Country, Brand FROM orders
  UNION
  SELECT Country, Brand FROM rev
)

SELECT cb.Country
     , cb.Brand
     , SUM(o.netprice) AS OrdersNet
     , SUM(CASE 
               WHEN o.isopen = 1
               THEN o.netprice
           END) AS OpenOrdersNet
     , SUM(CASE 
               WHEN r.bdate BETWEEN '2020-12-01' AND '2020-12-31'
               THEN r.netprice
           END) AS Revenue
     , SUM(CASE 
               WHEN r.bdate BETWEEN '2020-12-01' AND '2020-12-31'
               THEN r.rpro
           END) AS RawProceeds
     , SUM(CASE 
              WHEN r.bdate BETWEEN '2020-11-01' AND '2020-11-30'
              THEN r.netprice
           END) AS RevenueCompare
     , SUM(CASE 
              WHEN r.bdate BETWEEN '2020-11-01' AND '2020-11-30'
              THEN r.rpro
           END) AS RawProceedsCompare    
FROM cb
LEFT JOIN orders o
 ON cb.Country = o.Country
 AND cb.Brand = o.Brand
LEFT JOIN rev r
 ON cb.Country = r.Country
 AND cb.Brand = r.Brand
GROUP BY cb.Country
       , cb.Brand

SQL Fiddle

Upvotes: 2

Related Questions