Reconcile Cost and Usage to Invoice Detail by Invoice ID

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

InvoiceId and the separate Invoice Detail dataset containing InvoiceId and BilledCost are absent from the v1.0 sample, preventing invoice-level reconciliation.

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

Original FOCUS SQL

SELECT
  COALESCE(ID.InvoiceId, CU.InvoiceId) AS InvoiceId,
  ID.TotalBilledCost_InvoiceDetail,
  CU.TotalBilledCost_CostAndUsage,
  (COALESCE(ID.TotalBilledCost_InvoiceDetail, 0) - COALESCE(CU.TotalBilledCost_CostAndUsage, 0)) AS Variance
FROM (
  SELECT
    InvoiceId,
    SUM(BilledCost) AS TotalBilledCost_InvoiceDetail
  FROM InvoiceDetail
  WHERE
    ChargeCategory <> 'Tax'
  GROUP BY
    InvoiceId
) AS ID
FULL OUTER JOIN (
  SELECT
    InvoiceId,
    SUM(BilledCost) AS TotalBilledCost_CostAndUsage
  FROM CostAndUsage
  WHERE
    ChargeCategory <> 'Tax'
  GROUP BY
    InvoiceId
) AS CU
  ON ID.InvoiceId = CU.InvoiceId
WHERE
  COALESCE(ID.InvoiceId, CU.InvoiceId) = ?