Task
You are given monthly financial report data from two sources: the official ledger and the reporting export. Write a PostgreSQL query that identifies ledger entries that do not reconcile with the report for the same account and month, and shows the variance amount for each mismatch.
Return only rows where the ledger amount and report amount differ or where no matching report row exists. Treat missing report amounts as 0 when calculating variance.
Schema
| Table | Column | Type | Description |
|---|
ledger_entries | entry_id | INT | Primary key for the ledger row |
ledger_entries | account_code | VARCHAR(20) | GL account code |
ledger_entries | entry_month | DATE | First day of the accounting month |
ledger_entries | ledger_amount | NUMERIC(12,2) | Posted amount in the ledger |
report_exports | report_id | INT | Primary key for the report row |
report_exports | account_code | VARCHAR(20) | GL account code |
report_exports | report_month | DATE | First day of the accounting month |
report_exports | reported_amount | NUMERIC(12,2) | Amount shown in the financial report |
Sample data
| ledger_entries.entry_id | account_code | entry_month | ledger_amount |
|---|
| 1 | 4000-REV | 2024-01-01 | 12500.00 |
| 2 | 5000-COGS | 2024-01-01 | 4200.00 |
| 3 | 6100-OPEX | 2024-01-01 | 3100.00 |
| 4 | 4000-REV | 2024-02-01 | 13100.00 |
| 5 | 5000-COGS | 2024-02-01 | 4300.00 |
| 6 | 7000-OTHER | 2024-02-01 | 0.00 |
| report_exports.report_id | account_code | report_month | reported_amount |
|---|
| 11 | 4000-REV | 2024-01-01 | 12500.00 |
| 12 | 5000-COGS | 2024-01-01 | 4150.00 |
| 13 | 6100-OPEX | 2024-01-01 | 3100.00 |
| 14 | 4000-REV | 2024-02-01 | 13050.00 |
| 15 | 5000-COGS | 2024-02-01 | 4300.00 |
| 16 | 8000-ADJ | 2024-02-01 | 250.00 |
Expected output
| account_code | entry_month | ledger_amount | reported_amount | variance | status |
|---|
| 5000-COGS | 2024-01-01 | 4200.00 | 4150.00 | 50.00 | MISMATCH |
| 4000-REV | 2024-02-01 | 13100.00 | 13050.00 | 50.00 | MISMATCH |
| 7000-OTHER | 2024-02-01 | 0.00 | 0.00 | 0.00 | MISSING_REPORT |
