Kickstart 2020 with new opportunities! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Reports with revenue in categories
Revenue in time periods for selected categories
Summary

Instruction

Very well done! Now, our previous report showed the categories as columns and the time periods as rows. We can switch this, showing the time periods as columns and the categories as rows. The report will look like this:

CategoryName OverallCategoryRevenue Revenue2016 Revenue2017 Revenue2018
Confections 167357.26 29685.56 82657.75 55013.95
Meat/Poultry 163022.37 28813.66 80975.12 53233.59
Produce 99984.57 13885.78 54940.76 31158.03

Take a look at the query:

SELECT
  CategoryName,
  SUM(OI.Amount) AS OverallCategoryRevenue,
  SUM(CASE WHEN OrderDate >= '20160101' AND OrderDate < '20170101' THEN OI.Amount ELSE 0.0 END) AS Revenue2016,
  SUM(CASE WHEN OrderDate >= '20170101' AND OrderDate < '20180101' THEN OI.Amount ELSE 0.0 END) AS Revenue2017,
  SUM(CASE WHEN OrderDate >= '20180101' AND OrderDate < '20190101' THEN OI.Amount ELSE 0.0 END) AS Revenue2018
FROM OrderItems OI 
JOIN Orders O
  ON OI.OrderId = O.OrderId
JOIN Products P
  ON P.ProductId = OI.ProductId
JOIN Categories C
  ON C.CategoryId = P.CategoryId
GROUP BY C.CategoryId, CategoryName;

This time we don't group by time periods. Instead, we group by product categories. On the other hand, we have to define separate columns, using SUM(CASE WHEN...) for each period we want to compare.

This report format is useful when we have many categories to compare but few time periods.

Exercise

Show the category revenue for each quarter of 2017. Show the following columns: CategoryName, OverallCategoryRevenue, RevenueQ1, RevenueQ2, RevenueQ3, and RevenueQ4.

Stuck? Here's a hint!

You can check if a given order was placed in Q1 by using:

SUM(CASE
  WHEN DATEPART(Quarter, OrderDate) = 1
    THEN OI.Amount
  ELSE 0.0
END) AS RevenueQ1

Remember about filtering the rows for orders from 2017.