2.7 Cost Comparison

2.7.1 Description

2.7.2 Directly Dependent Columns

2.7.3 Supporting Columns

2.7.4 Example SQL Query

Example SQL Query

WITH AggregatedData AS (
  SELECT
    ServiceProviderName,
    BillingAccountId,
    BillingAccountName,
    BillingCurrency,
    ServiceName,
    SUM(EffectiveCost) AS TotalEffectiveCost,
    SUM(BilledCost) AS TotalBilledCost,
    SUM(CASE
          WHEN ChargeCategory = 'Usage' AND BilledCost = 0 AND EffectiveCost != 0
          THEN 0
          ELSE ContractedCost
        END) AS TotalContractedCost,
    SUM(CASE
          WHEN ChargeCategory = 'Usage' AND BilledCost = 0 AND EffectiveCost != 0
          THEN 0
          ELSE ListCost
        END) AS TotalListCost
  FROM focus_data_table
  WHERE BillingPeriodStart >= ?
    AND BillingPeriodEnd < ?
    AND ChargeClass IS NULL
  GROUP BY
    ServiceProviderName,
    BillingAccountId,
    BillingAccountName,
    BillingCurrency,
    ServiceName
)
SELECT ServiceProviderName,
    BillingAccountId,
    BillingAccountName,
    BillingCurrency,
    ServiceName,
    TotalEffectiveCost,
    TotalBilledCost,
    TotalListCost,
    1 - (TotalContractedCost / NULLIF(TotalListCost, 0)) * 100 AS ContractedDiscount,
    1 - (TotalEffectiveCost / NULLIF(TotalListCost, 0)) * 100 AS EffectiveDiscount
FROM AggregatedData