Identify unused commitments

This query uses the ProviderName column. ProviderName is deprecated in v1.3 and will be removed in v1.4.

FOCUS Versions

v1.0 v1.1 v1.2

Context

Identify all data in a time period where CommitmentDiscountStatus is ‘unused’ thus identifying commitments that aren’t used and enabling the Practitioner to take action on either selling the commitment (if able), working with other personas on a workload that could use the commitment, or performing informed ignore (knowing it is there but ignoring it because there is nothing one can do).

FOCUS SQL Query

SELECT
  MIN(ChargePeriodStart) AS ChargePeriodStart,
  MAX(ChargePeriodEnd) AS ChargePeriodEnd,
  ProviderName,
  BillingAccountId,
  CommitmentDiscountId,
  CommitmentDiscountType,
  CommitmentDiscountStatus,
  SUM(BilledCost) AS TotalBilledCost,
  SUM(EffectiveCost) AS TotalEffectiveCost
FROM focus_data_table
WHERE ChargePeriodStart >= ? AND ChargePeriodEnd < ?
  AND CommitmentDiscountStatus = 'Unused'
GROUP BY
  ProviderName,
  BillingAccountId,
  CommitmentDiscountId,
  CommitmentDiscountType,
  CommitmentDiscountStatus