Validates that the sum of costs for all service usage in the Cost and Usage dataset equals the total non-tax charges in the Invoice Detail dataset for a specific invoice.
Column names from the source use case.
InvoiceId and the separate Invoice Detail dataset containing InvoiceId and BilledCost are absent from the v1.0 sample, preventing invoice-level reconciliation.
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) = ?