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
13. Creating new columns with custom values
Summary

Instruction

There's one more report type we want to show you in this part: the revenue-in-quarters report. First, however, we need to introduce a SQL construction you might not have heard about. Take a look:

SELECT
  OrderId,
  CASE
    WHEN ShipCountry IN ('USA', 'Canada', 'Mexico')
      THEN 'North America' 
    ELSE 'Elsewhere'
  END AS Continent
FROM Orders;

We'll obtain the following result:

OrderId Continent
... ...
10258 Elsewhere
10259 North America
10260 Elsewhere
... ...

The construction:

CASE
  WHEN expression
    THEN result_true
  ELSE result_false
END

checks if the expression part is true. If it is, the result_true part from the THEN clause is used. Otherwise, the result_false part from the ELSE clause is used. In this case, we check the value in the ShipCountry column. Based on that value, we set the value of the new column to either 'North America' or 'Elsewhere'.

Exercise

For each order shipped to Germany, show three columns:

  1. OrderId
  2. Amount
  3. OrderValue – set this value to 'high' if the order's amount is greater than $1,000. Otherwise, set it to 'low'.

Stuck? Here's a hint!

Use the following code for the last column:

CASE
  WHEN Amount > 1000.0
    THEN 'high' 
  ELSE 'low'
END AS OrderValue