Join Contract Commitment Details With Usage Charges

Source: FOCUS use-case SQL
Status: Not in v1.0 sample
Results: Not run
Mode: Read-only
Library snapshot (UTC): 2026-10-08

ContractApplied and the Contract Commitment dataset fields ContractCommitmentId, ContractCommitmentCategory, ContractCommitmentPeriodStart, ContractCommitmentPeriodEnd, and ContractCommitmentCost are absent from the v1.0 sample.

Review the original SQL below and the required columns before using a newer FOCUS dataset.

Original FOCUS SQL

SELECT
  CU.ServiceProviderName,
  JSON_VALUE(CA, '$.ContractCommitmentID') AS ContractCommitmentId,
  CC.ContractCommitmentCategory,
  CC.ContractCommitmentPeriodStart,
  CC.ContractCommitmentPeriodEnd,
  CC.ContractCommitmentCost,
  SUM(CU.EffectiveCost) AS TotalUsageCost,
  SUM(CAST(JSON_VALUE(CA, '$.ContractCommitmentAppliedCost') AS FLOAT64)) AS AppliedCost
 FROM focus_data_table CU
 CROSS JOIN
  UNNEST(JSON_EXTRACT_ARRAY(CU.ContractApplied, '$.Elements')) AS CA
 JOIN contract_commitment CC
  ON JSON_VALUE(CA, '$.ContractCommitmentID') = CC.ContractCommitmentId
 WHERE CU.ChargePeriodStart >= ? AND CU.ChargePeriodEnd < ?
 GROUP BY
  CU.ServiceProviderName,
  JSON_VALUE(CA, '$.ContractCommitmentID'),
  CC.ContractCommitmentCategory,
  CC.ContractCommitmentPeriodStart,
  CC.ContractCommitmentPeriodEnd,
  CC.ContractCommitmentCost