Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Total revenue for multiple periods
Calculating deltas
Creating "revenue in quarters" reports
Summary
17. Recap

## Instruction

That's all we wanted to teach you in this part. It's time to wrap things up! First, let's review what we've learned:

1. To show the total revenue for each year, use DATEPART():
SELECT
DATEPART(Year, OrderDate) AS RevenueYear,
SUM(Amount) AS TotalRevenue
FROM Orders
GROUP BY DATEPART(Year, OrderDate)
ORDER BY DATEPART(Year, OrderDate);

2. To show the total revenue for each quarter/month in each year, use DATEPART() twice:
SELECT
DATEPART(Year, OrderDate) AS RevenueYear,
DATEPART(Quarter, OrderDate) AS RevenueQuarter,
...
GROUP BY
DATEPART(Year, OrderDate),
DATEPART(Quarter, OrderDate);

3. To show the total revenue on all aggregation levels, add ROLLUP():
...
GROUP BY ROLLUP(
DATEPART(Year, OrderDate),
DATEPART(Quarter, OrderDate)
)
...

4. To show the revenue from the previous year, use LAG():
LAG(SUM(Amount), 1) OVER (ORDER BY DATEPART(Year, OrderDate))

5. To calculate the difference between the current period and the previous period, use:
SUM(Amount) - LAG(SUM(Amount), 1) OVER (ORDER BY DATEPART(Year, OrderDate)) AS Delta

6. To calculate the difference between the current period and the previous period as a percentage, use:
ROUND(100 * (
SUM(Amount) - LAG(SUM(Amount), 1) OVER (ORDER BY DATEPART(Year, OrderDate)) /
CAST(LAG(SUM(Amount), 1) OVER (ORDER BY DATEPART(Year, OrderDate)) AS float)),
2) AS DeltaPercentage

7. To create a revenue in quarters report, use:
SELECT
DATEPART(Year, OrderDate) AS Year,
SUM(CASE WHEN DATEPART(Quarter, OrderDate) = 1 THEN Amount ELSE 0 END) AS Q1,
...
FROM Orders
GROUP BY DATEPART(Year, OrderDate)
ORDER BY DATEPART(Year, OrderDate);


How about a quick quiz?

## Exercise

Click to continue. 