Kickstart 2020 with new opportunities! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Total revenue for multiple periods
Calculating deltas
Creating "revenue in quarters" reports
Summary

Instruction

Perfect! Now we'd like to show the quarterly revenue in each year. To do that, we could use the following query:

SELECT
  DATEPART(Year, OrderDate) AS RevenueYear, 
  DATEPART(Quarter, OrderDate) AS RevenueQuarter, 
  SUM(Amount) AS TotalRevenue 
FROM Orders
GROUP BY DATEPART(Year, OrderDate),
  DATEPART(Quarter, OrderDate)
ORDER BY DATEPART(Year, OrderDate), 
  DATEPART(Quarter, OrderDate);

We would get a result like this:

RevenueYear RevenueQuarter TotalRevenue
... ... ...
2016 4 128355.40
2017 1 138288.95
2017 2 143177.03
... ... ...

As you can see, we used DATEPART() twice, with either Year or Quarter as the first argument. We also group all rows by both the Year and Quarter columns.

Exercise

Show the total monthly revenue in each year, but only for orders processed by the employee with ID of 5.

Order the results by month.

Stuck? Here's a hint!

Use DATEPART twice, once with Year and once with Month.