Kickstart 2020 with new opportunities! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Get to know the database
Basic revenue metrics
Summary

Instruction

Good job! The last thing we'll show you in this part is how to do revenue reports for the current year, month, etc. They're another frequent financial report, and they show how much the company earned so far in the given current period. Commonly used report types are:

  • year-to-date (YTD) – refers to the period beginning the first day of the current calendar year up to the current date.
  • month-to-date (MTD) – refers to the period of time between the 1st of the current month and the current date.
  • quarter-to-date (QTD) – refers to the period of time between the beginning of the current quarter and the current date.

YTD, MTD, and QTD reports are used by business owners, investors, and individuals to analyze their revenue, income, business earnings, and investment returns for the current period of time.

For example, if we want to show the month-to-date revenue:

SELECT
  C.CustomerId,
  CompanyName,
  SUM(Amount) AS TotalRevenue 
FROM Orders O
JOIN Customers C
  ON O.CustomerId = C.CustomerId
WHERE OrderDate >= DATEADD(Month, DATEDIFF(Month, 0, GETDATE()), 0)
GROUP BY C.CustomerId, CompanyName;

If you look at the WHERE clause, you can see we used the technique we learned in the previous exercise to compute the beginning of the current month.

Exercise

Calculate the year-to-date revenue for each customer. Show two columns: CustomerId and TotalRevenue.

Stuck? Here's a hint!

Use the following condition in the WHERE clause:

OrderDate >= DATEADD(Year, DATEDIFF(Year, 0, GETDATE()), 0)