Year over Year using CTE

Question

Hi : I am trying to calculate YOY sales using AdventureWorks DB. The following code works aggregate at Year & Month level.

However, in the query I need to add the following fields ProductCatgoryName, ProductSubCatgoryName & ProductName in my existing query. I am not sure how to achieve it. Any assistance would be greatly appreciated. Please let me know if there are other better ways to achieve the same outcome other than using CTE.

Many Thanks

-- Year over Year Calculations
USE AdventureWorksDW2014;
GO

-- Get Prev Year Sales
WITH MonthlySales (YearNum, MonthNum, Sales)
AS
(
SELECT d.CalendarYear, d.MonthNumberOfYear, SUM(s.SalesAmount)
FROM DimDate d
JOIN FactInternetSales s ON d.DateKey = s.OrderDateKey
GROUP BY d.CalendarYear, d.MonthNumberOfYear
)
-- Get Current Year and join to CTE for previous year
SELECT
d.CalendarYear
, d.MonthNumberOfYear
, ms.Sales PrevSales
,
FROM DimDate d
JOIN FactInternetSales s ON d.DateKey = s.OrderDateKey
JOIN MonthlySales ms ON
d.CalendarYear-1 = ms.YearNum AND
d.MonthNumberOfYear = ms.MonthNum
GROUP BY
d.CalendarYear
, d.MonthNumberOfYear
, ms.Sales
ORDER BY
1 DESC, 2 DESC

Answer 1

Check if the next query gives the results and can be adjusted:

;
with MonthlySales as
(
    select d.CalendarYear, d.MonthNumberOfYear, s.ProductKey, sum(s.SalesAmount) as SalesAmount
    from FactInternetSales s
    inner join DimDate d on d.DateKey = s.OrderDateKey
    group by d.CalendarYear, d.MonthNumberOfYear, s.ProductKey
)
select ms.CalendarYear, ms.MonthNumberOfYear, 
    p.EnglishProductName, pc.EnglishProductCategoryName, psc.EnglishProductSubcategoryName,
    ms.SalesAmount as SalesAmount,
    isnull(pms.SalesAmount, 0) as SalesAmountPreviousYear
from MonthlySales ms
inner join DimProduct p on p.ProductKey = ms.ProductKey
inner join DimProductSubcategory psc on psc.ProductSubcategoryKey = p.ProductSubcategoryKey
inner join DimProductCategory pc on pc.ProductCategoryKey = psc.ProductCategoryKey
left join MonthlySales pms on pms.ProductKey = ms.ProductKey and pms.CalendarYear = ms.CalendarYear-1 and pms.MonthNumberOfYear = ms.MonthNumberOfYear
order by CalendarYear, ms.MonthNumberOfYear, p.EnglishProductName

Use inner join instead of left join if you are only interested in products that have current and previous year sales.