You are given a bank reconciliation dataset with ledger entries, bank statement transactions, and reconciliation runs. Write a PostgreSQL query that validates whether each reconciliation run is complete and accurate by comparing ledger and bank transactions for the same account and statement period.
Return one row per reconciliation run with the total ledger amount, total bank amount, the variance between them, the count of unmatched ledger items, the count of unmatched bank items, and a status of complete only when both sides balance and there are no unmatched items.
| table | column | type | description |
|---|---|---|---|
| reconciliation_runs | reconciliation_run_id | INT | Primary key for the reconciliation run |
| reconciliation_runs | account_id | INT | Account being reconciled |
| reconciliation_runs | statement_start_date | DATE | Start of the statement period |
| reconciliation_runs | statement_end_date | DATE | End of the statement period |
| reconciliation_runs | run_date | DATE | Date the reconciliation was performed |
| ledger_entries | ledger_entry_id | INT | Primary key for the ledger entry |
| ledger_entries | account_id | INT | Account associated with the entry |
| ledger_entries | entry_date | DATE | Ledger posting date |
| ledger_entries | amount | NUMERIC(12,2) | Signed ledger amount |
| ledger_entries | bank_reference | VARCHAR(50) | Bank reference if matched; may be NULL |
| bank_transactions | bank_transaction_id | INT | Primary key for the bank transaction |
| bank_transactions | account_id | INT | Account associated with the transaction |
| bank_transactions | transaction_date | DATE | Bank transaction date |
| bank_transactions | amount | NUMERIC(12,2) | Signed bank amount |
| bank_transactions | bank_reference | VARCHAR(50) | Bank reference if matched; may be NULL |
reconciliation_runs
| reconciliation_run_id | account_id | statement_start_date | statement_end_date | run_date |
|---|---|---|---|---|
| 1 | 101 | 2024-01-01 | 2024-01-31 | 2024-02-02 |
| 2 | 102 | 2024-01-01 | 2024-01-31 | 2024-02-02 |
| 3 | 103 | 2024-01-01 | 2024-01-31 | 2024-02-02 |
ledger_entries
| ledger_entry_id | account_id | entry_date | amount | bank_reference |
|---|---|---|---|---|
| 11 | 101 | 2024-01-05 | 500.00 | BR-1001 |
| 12 | 101 | 2024-01-12 | -75.00 | BR-1002 |
| 13 | 101 | 2024-01-20 | 25.00 | NULL |
| 14 | 102 | 2024-01-03 | 200.00 | BR-2001 |
| 15 | 102 | 2024-01-18 | 50.00 | NULL |
bank_transactions
| bank_transaction_id | account_id | transaction_date | amount | bank_reference |
|---|---|---|---|---|
| 21 | 101 | 2024-01-05 | 500.00 | BR-1001 |
| 22 | 101 | 2024-01-12 | -75.00 | BR-1002 |
| 23 | 101 | 2024-01-28 | 10.00 | BR-9999 |
| 24 | 102 | 2024-01-03 | 200.00 | BR-2001 |
| 25 | 102 | 2024-01-18 | 50.00 | NULL |
| reconciliation_run_id | account_id | ledger_total | bank_total | variance | unmatched_ledger_count | unmatched_bank_count | reconciliation_status |
|---|---|---|---|---|---|---|---|
| 1 | 101 | 450.00 | 435.00 | 15.00 | 1 | 1 | exception |
| 2 | 102 | 250.00 | 250.00 | 0.00 | 1 | 1 | exception |
| 3 | 103 | 0.00 | 0.00 | 0.00 | 0 | 0 | complete |
You are given a bank reconciliation dataset with ledger entries, bank statement transactions, and reconciliation runs. Write a PostgreSQL query that validates whether each reconciliation run is complete and accurate by comparing ledger and bank transactions for the same account and statement period.
Return one row per reconciliation run with the total ledger amount, total bank amount, the variance between them, the count of unmatched ledger items, the count of unmatched bank items, and a status of complete only when both sides balance and there are no unmatched items.
| table | column | type | description |
|---|---|---|---|
| reconciliation_runs | reconciliation_run_id | INT | Primary key for the reconciliation run |
| reconciliation_runs | account_id | INT | Account being reconciled |
| reconciliation_runs | statement_start_date | DATE | Start of the statement period |
| reconciliation_runs | statement_end_date | DATE | End of the statement period |
| reconciliation_runs | run_date | DATE | Date the reconciliation was performed |
| ledger_entries | ledger_entry_id | INT | Primary key for the ledger entry |
| ledger_entries | account_id | INT | Account associated with the entry |
| ledger_entries | entry_date | DATE | Ledger posting date |
| ledger_entries | amount | NUMERIC(12,2) | Signed ledger amount |
| ledger_entries | bank_reference | VARCHAR(50) | Bank reference if matched; may be NULL |
| bank_transactions | bank_transaction_id | INT | Primary key for the bank transaction |
| bank_transactions | account_id | INT | Account associated with the transaction |
| bank_transactions | transaction_date | DATE | Bank transaction date |
| bank_transactions | amount | NUMERIC(12,2) | Signed bank amount |
| bank_transactions | bank_reference | VARCHAR(50) | Bank reference if matched; may be NULL |
reconciliation_runs
| reconciliation_run_id | account_id | statement_start_date | statement_end_date | run_date |
|---|---|---|---|---|
| 1 | 101 | 2024-01-01 | 2024-01-31 | 2024-02-02 |
| 2 | 102 | 2024-01-01 | 2024-01-31 | 2024-02-02 |
| 3 | 103 | 2024-01-01 | 2024-01-31 | 2024-02-02 |
ledger_entries
| ledger_entry_id | account_id | entry_date | amount | bank_reference |
|---|---|---|---|---|
| 11 | 101 | 2024-01-05 | 500.00 | BR-1001 |
| 12 | 101 | 2024-01-12 | -75.00 | BR-1002 |
| 13 | 101 | 2024-01-20 | 25.00 | NULL |
| 14 | 102 | 2024-01-03 | 200.00 | BR-2001 |
| 15 | 102 | 2024-01-18 | 50.00 | NULL |
bank_transactions
| bank_transaction_id | account_id | transaction_date | amount | bank_reference |
|---|---|---|---|---|
| 21 | 101 | 2024-01-05 | 500.00 | BR-1001 |
| 22 | 101 | 2024-01-12 | -75.00 | BR-1002 |
| 23 | 101 | 2024-01-28 | 10.00 | BR-9999 |
| 24 | 102 | 2024-01-03 | 200.00 | BR-2001 |
| 25 | 102 | 2024-01-18 | 50.00 | NULL |
| reconciliation_run_id | account_id | ledger_total | bank_total | variance | unmatched_ledger_count | unmatched_bank_count | reconciliation_status |
|---|---|---|---|---|---|---|---|
| 1 | 101 | 450.00 | 435.00 | 15.00 | 1 | 1 | exception |
| 2 | 102 | 250.00 | 250.00 | 0.00 | 1 | 1 | exception |
| 3 | 103 | 0.00 | 0.00 | 0.00 | 0 | 0 | complete |
