Awesome! We can also express the delta between two periods as a percentage. Look at the following query:
DATEPART(Year, OrderDate) AS RevenueYear,
SUM(Amount) AS TotalRevenue,
(SUM(Amount) - LAG(SUM(Amount), 1)
OVER (ORDER BY DATEPART(Year, OrderDate)))
/ CAST(SUM(Amount) AS decimal(10,2)),
2) AS DeltaPercentage
GROUP BY DATEPART(Year, OrderDate)
ORDER BY DATEPART(Year, OrderDate);
Here is the result:
This time, the calculation in the last column is a bit more complicated:
- We divide the change between the current and previous period by the revenue value from the current period.
- To avoid integer division, we cast the denominator to a decimal type with
CAST(SUM(Amount) AS decimal(10,2))). For larger numbers, you can of course increase the first argument of
- We multiply the division result by
100 to get the percentage value.
- We round the percentage to two decimal places using the
ROUND(value, 2) function.