Best April deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Get to know the database
Basic revenue metrics
Summary

Instruction

Perfect! When we wanted to find orders from March 2017, we used the following WHERE clause:

WHERE o.order_date >= '2017-03-01' AND o.order_date < '2017-04-01'

There is another way to write this query. Take a look:

SELECT
  c.customer_id,
  company_name,
  SUM(amount) AS total_revenue 
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id
WHERE o.order_date >= '20170301'
AND o.order_date < CAST('2017-03-01' AS DATE) + INTERVAL '1' month
GROUP BY c.customer_id, company_name;

This time, instead of providing the specific end date, we used the INTERVAL keyword:

CAST(date AS DATE) + INTERVAL 'number' interval

where:

  1. interval – the interval we want to add, such as year, month or day.
  2. number – the amount of that interval to add.
  3. date – the date to be modified.

In our example, CAST('2017-03-01' AS DATE) + INTERVAL '1' month means "add one month to March 1, 2017".

Exercise

The fiscal year in Northwind starts on September 1.

For each customer, show the total revenue from orders placed in the fiscal year starting September 1, 2016. Show three columns: customer_id, company_name, and total_revenue.

Use the INTERVAL keyword.

Stuck? Here's a hint!

Use the following code with INTERVAL:

order_date < CAST('2016-09-01' AS DATE) + INTERVAL '1' year