[SQL] Comparing current quarter with the same quarter from last year

I have a common task that I have to do which involves comparing the current quarter's results with those of the same quarter from last year. I have written this query, but am only getting half the results that I want.

WITH orders_cy AS (
SELECT
prod.ProductID,
prodc.[ProductCategoryID],
sod.[Year],
sod.[Quarter],
sod.OrderQty as qty_cy
FROM SalesLT.SalesOrderDetail sod
LEFT JOIN SalesLT.Product prod ON sod.ProductID = prod.ProductID
LEFT JOIN SalesLT.ProductCategory prodc ON prod.ProductCategoryID = prodc.ProductCategoryID
WHERE prodc.ProductCategoryID = 5 AND DATEPART(YY,sod.Saledate) = DATEPART(YY,CURRENT_TIMESTAMP)
),
orders_ly as (
SELECT
prod.ProductID,
prodc.[ProductCategoryID],
sod.[Year],
sod.[Quarter],
sod.OrderQty as qty_ly
FROM SalesLT.SalesOrderDetail sod
LEFT JOIN SalesLT.Product prod ON sod.ProductID = prod.ProductID
LEFT JOIN SalesLT.ProductCategory prodc ON prod.ProductCategoryID = prodc.ProductCategoryID
WHERE prodc.ProductCategoryID = 5 AND DATEPART(YY,sod.Saledate) = DATEPART(YY,CURRENT_TIMESTAMP)-1
)

SELECT
orders_cy.year,
orders_cy.quarter,
sum(orders_cy.qty_cy) as FQSales,
sum(orders_ly.qty_ly) as FQSalesLY,
sum(orders_cy.qty_cy) - sum(orders_ly.qty_ly) as qoq_Diff_Value,
CAST(sum(orders_cy.qty_cy) * 1.0 / sum(orders_ly.qty_ly) -1 AS NUMERIC(18,2)) * 100 AS qoq_DIFF_PERC
FROM orders_cy
FULL JOIN orders_ly ON
orders_cy.ProductID = orders_ly.ProductID
AND orders_cy.Quarter = orders_ly.Quarter
GROUP BY orders_cy.ProductCategoryID, orders_cy.[Year], orders_cy.quarter


The first CTE (orders_cy) returns this data:

ProductID ProductCategoryID Year Quarter Sales
779 5 2024 3 3
780 5 2024 1 1
785 5 2024 3 2
780 5 2024 4 3
781 5 2024 4 4
782 5 2024 4 2
783 5 2024 3 5
783 5 2024 4 15
784 5 2024 1 5
981 5 2024 1 1
981 5 2024 2 2
981 5 2024 4 2
985 5 2024 4 2
985 5 2024 2 5
987 5 2024 3 5
988 5 2024 4 11

The second CTE (orders_ly) returns this data:

ProductID ProductCategoryID Year Quarter Sales
779 5 2023 1 1
779 5 2023 2 4
779 5 2023 3 3
780 5 2023 2 5
781 5 2023 2 2
782 5 2023 1 3
783 5 2023 3 7
980 5 2023 2 3
981 5 2023 3 2
982 5 2023 1 3
983 5 2023 1 3
984 5 2023 1 1
985 5 2023 3 2
988 5 2023 1 1
988 5 2023 3 2
991 5 2023 3 5
992 5 2023 3 3
993 5 2023 2 1

The final result should be:

Year Quarter FQSales FYSalesLY QOQ_DiffValue QOQ_DiffPerc
NULL NULL NULL 41 NULL NULL
2024 1 7 NULL NULL NULL
2024 2 7 NULL NULL NULL
2024 3 15 10 5 50.00
2024 4 39 NULL NULL NULL

I am supposed to see 12, 15, and 25 in FYSalesLY and the first row above with the 41 should be gone. I think the issue that I have is with the joins. The tables that I have are as follows:

SalesLT.SalesOrderDetail (Contains the sale dates with year, quarter, month)

SalesLT.Product (Contains the product IDs. There are multiple product IDs per broader category ID)

SalesLT.ProductCategory (Lists the product categories corresponding to each product ID)

The CTEs that I use return the correct results for the respective years. The problem emerges when I try to select from both CTEs. Since the sales of some product IDs may not be the same in both years, I figured a FULL OUTER JOIN would be appropriate since I simply want to compare sales for products with the same product category between years. Using a LEFT or RIGHT outer join would lead to the exclusion of some sales records.

PS. If someone could point me to how I can format tables more quickly rather than entering the delimiters in between data points manually that would be great.

Continue reading...