Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Revenue Trend Analysis in SQL Server

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.