Determine Effective Savings Rate by Service
This query uses the Provider column. Provider is deprecated in v1.3 and will be removed in v1.4.
FOCUS Versions
v1.0
v1.1
v1.2
Context
This use cases focuses on Effective Savings Rate (ESR) per Service.
The Effective Savings Rate is a KPI to track the efficiency of using commitment-based discounts. The KPI calculation is (Contracted Cost – Effective Cost) / Contracted Cost)
FOCUS SQL Query
SELECT
ProviderName,
ServiceName,
SUM(ContractedCost) AS TotalContractedCost,
SUM(EffectiveCost) AS TotalEffectiveCost,
(SUM(ContractedCost) - SUM(EffectiveCost)) / NULLIF(SUM(ContractedCost), 0) AS EffectiveSavingsRate
FROM focus_data_table
WHERE ChargePeriodStart >= ? AND ChargePeriodEnd < ?
GROUP BY
ProviderName,
ServiceName