Compare the virtual currency discounted rate to the virtual currency list price to determine the difference, as contracts can lower the virtual-currency price of certain operations.
Column names from the source use case.
PricingCurrency, PricingCurrencyListUnitPrice, PricingCurrencyContractedUnitPrice and ServiceSubcategory are absent from the v1.0 sample, so savings in a virtual pricing currency cannot be computed.
SELECT
ServiceName,
ServiceSubcategory,
ChargeDescription,
BillingCurrency,
PricingCurrency,
SUM(PricingCurrencyListUnitPrice-PricingCurrencyContractedUnitPrice) AS ContractedSavingsInPricingCurrency,
SUM(ListUnitPrice - ContractedUnitPrice) AS ContractedSavingsInBillingCurrency
FROM focus_data_table
WHERE ChargePeriodStart >= ? and ChargePeriodEnd < ?
AND PricingCurrencyListUnitPrice > PricingCurrencyContractedUnitPrice
GROUP BY
ServiceName,
ServiceSubcategory,
ChargeDescription,
BillingCurrency,
PricingCurrency