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

Good job! Now, let's say we want to compare revenue changes month by month. We can pick a single year and show the revenue for each month. Check it out:

SELECT
  DATEPART(Month, OrderDate) AS MonthIn2016,
  SUM(Amount) AS TotalRevenue 
FROM Orders
WHERE OrderDate >= '20160101' AND OrderDate < '20170101'
GROUP BY DATEPART(Month, OrderDate)
ORDER BY DATEPART(Month, OrderDate);

The result looks like this:

MonthIn2016 TotalRevenue
... ...
8 25485.28
9 26381.40
10 37515.73
... ...

To extract the months, we used DATEPART(Month, OrderDate) instead of DATEPART(Year, OrderDate). Note two things:

  1. Months are shown as integers from 1 to 12.
  2. Months don't contain any information about the year, so we typically pick a single year using WHERE (as we did in the above example.) If you don't use WHERE to select the year, you'd get monthly revenue values summed across all years. In other words, the January revenue would show the sum of January 2016, January 2017, and January 2018.

Exercise

We can also use DATEPART() to get quarterly info, which is frequently used in financial analyses. The statement below ...

DATEPART(Quarter, date) = 1

... signifies the period from January through March. The following statement:

DATEPART(Quarter, date) = 2

... signifies the period from April through June. And so on for the other two quarters.

Show the total revenue from each quarter of 2017. Show two columns: QuarterIn2017 and TotalRevenue.

Order the rows by quarter.

Stuck? Here's a hint!

Use the word Quarter as the first argument of the DATEPART() function.