Best April deals - 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

Well done! We can create another version of the same report, this one showing revenue deltas. We'd like to get a report like this:

CategoryName OverallCategoryRevenue Revenue2016 Change2017 Change2018
Confections 167357.26 29685.56 52972.19 -27643.80
Meat/Poultry 163022.37 28813.66 52161.46 -27741.53

To that end, we can use the query below:

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)
    - SUM(CASE WHEN OrderDate >= '20160101' AND OrderDate < '20170101' THEN OI.Amount ELSE 0.0 END) AS Change2017,

  SUM(CASE WHEN OrderDate >= '20180101' AND OrderDate < '20190101' THEN OI.Amount ELSE 0.0 END)
    - SUM(CASE WHEN OrderDate >= '20170101' AND OrderDate < '20180101' THEN OI.Amount ELSE 0.0 END) AS Change2018

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;

As you can see, the last two columns subtract the previous year's sum from the current year's sum.

Exercise

Create a report similar to the one shown in the explanation. This time, however, show the revenue in the first quarter of 2017 and the revenue changes in the next three quarters of 2017. Show the following columns: CategoryName, OverallCategoryRevenue2017, Revenue2017Q1, ChangeQ2, ChangeQ3, and ChangeQ4.

Stuck? Here's a hint!

In the WHERE clause, filter for orders from 2017. Then, use DATEPART(Quarter, OrderDate) to get orders from each quarter.