First, let's see how our revenue has changed over time. To do so, we'll compare revenue values between consecutive years, months, or any other time periods. Such reports are commonly called year-to-year, quarter-to-quarter, and month-to-month reports. In Microsoft SQL Server, we can use the
DATEPART() function to create such reports. Take a look:
DATEPART(Year, OrderDate) AS RevenueYear,
SUM(Amount) AS TotalRevenue
GROUP BY DATEPART(Year, OrderDate)
ORDER BY DATEPART(Year, OrderDate);
And the result:
DATEPART(time unit, date) function takes two parameters: a time unit (such as
Month, etc.) and a date or time column. As the name suggests, the function returns the specified part of a given date.
In our example,
DATEPART(Year, OrderDate) returns the year when the order was placed (2016, 2017, or 2018). As you can see, we also use the
DATEPART() function to group all the orders and sum the revenue values for each year.
Note that we also added an
ORDER BY clause to make sure the revenue values are shown in chronological order.