End of Summer - 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 Next exercise to continue.