Introduction
Reports with revenue in categories
Revenue in time periods for selected categories
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.
