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 >= '2017-03-01'
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:
interval – the interval we want to add, such as year, month or day.number – the amount of that interval to add.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".
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.
Use the following code with INTERVAL:
order_date < CAST('2016-09-01' AS DATE) + INTERVAL '1' year
