Lookup-Based Data Reconciliation

HardSQL · PostgreSQL

Problem

How do you utilize XLOOKUP and VLOOKUP for large-scale financial or operational data reconciliation?

Translate the lookup-based reconciliation approach into PostgreSQL. Compare corresponding records from two financial sources and classify matches, discrepancies, and records missing from either source.

Output

One row per transaction key found in either source.
Include the transaction key, both source dates, both amounts, amount difference, and reconciliation status.
Include matches and unmatched records, ordered by transaction key ascending.

Output details

Return columns in this order: transaction_id, erp_transaction_date, finance_posted_date, erp_amount, finance_amount, amount_difference, reconciliation_status.
Label missing ERP records MISSING_IN_ERP and missing finance records MISSING_IN_FINANCE.
Label present records MISMATCH when amounts, currencies, or dates differ; otherwise label them MATCH.
Treat NULL values as equal when both corresponding values are NULL, but as distinct from non-NULL values.

Schema

erp_transactions

Column	Type	Description
transaction_idPK	VARCHAR(20)	Transaction identifier from the ERP source
transaction_date	DATE	Date recorded by the ERP source
amount	NUMERIC(12,2)	Transaction amount recorded by the ERP source
currency	VARCHAR(3)	ISO currency code

finance_transactions

Column	Type	Description
transaction_idPK	VARCHAR(20)	Transaction identifier from the finance source
posted_date	DATE	Date posted by the finance source
amount	NUMERIC(12,2)	Transaction amount recorded by the finance source
currency	VARCHAR(3)	ISO currency code

Tableserp_transactionsfinance_transactions