Report on initial contract commitments

FOCUS Versions

v1.3 v1.4

Context

Aggregate initial contract commitment purchases to understand total contracted spend and usage commitments by service provider and commitment ID over a specified time range.

FOCUS SQL Query

SELECT
  MIN(CU.ChargePeriodStart) AS ChargePeriodStart,
  MAX(CU.ChargePeriodEnd) AS ChargePeriodEnd,
  CU.ServiceProviderName,
  JSON_VALUE(CA, '$.ContractCommitmentID') AS ContractCommitmentId,
  SUM(CAST(JSON_VALUE(CA, '$.ContractCommitmentAppliedCost') AS FLOAT64)) AS ContractCommitmentAppliedCost
 FROM focus_data_table CU
 CROSS JOIN
  UNNEST(JSON_EXTRACT_ARRAY(CU.ContractApplied, '$.Elements')) AS CA
 WHERE JSON_VALUE(CA, '$.ContractCommitmentAppliedCost') IS NOT NULL
  AND ChargePeriodStart >= ? AND ChargePeriodEnd < ?
  AND ChargeCategory = 'Purchase'
 GROUP BY
  CU.ServiceProviderName,
  JSON_VALUE(CA, '$.ContractCommitmentID')