Task
You are given ERP actuals and a planning tool extract for the same fiscal months. Write a PostgreSQL query that reconciles the two sources by account_code and fiscal_month, returns the actual amount, planned amount, and variance, and flags each row as matched, actual_only, plan_only, or mismatch when the numbers do not tie.
Schema
| table | column | type | description |
|---|
| erp_actuals | actual_id | INT | Primary key for the ERP posting |
| erp_actuals | account_code | VARCHAR(20) | General ledger account code |
| erp_actuals | fiscal_month | DATE | Month bucket for the actuals |
| erp_actuals | actual_amount | DECIMAL(12,2) | Posted ERP amount |
| erp_actuals | source_system | VARCHAR(50) | ERP source name |
| plan_forecast | plan_id | INT | Primary key for the plan row |
| plan_forecast | account_code | VARCHAR(20) | Planning account code |
| plan_forecast | fiscal_month | DATE | Month bucket for the plan |
| plan_forecast | planned_amount | DECIMAL(12,2) | Planned amount from the planning tool |
| plan_forecast | version_name | VARCHAR(50) | Plan version |
Sample data
erp_actuals
| actual_id | account_code | fiscal_month | actual_amount | source_system |
|---|
| 1 | 4000 | 2024-01-01 | 1000.00 | SAP |
| 2 | 4000 | 2024-02-01 | 1200.00 | SAP |
| 3 | 5000 | 2024-01-01 | 800.00 | SAP |
| 4 | 5000 | 2024-03-01 | 900.00 | SAP |
| 5 | 6000 | 2024-02-01 | 0.00 | SAP |
| 6 | 7000 | 2024-01-01 | 450.00 | SAP |
| 7 | 8000 | 2024-03-01 | 300.00 | SAP |
| 8 | 9000 | 2024-02-01 | 150.00 | SAP |
plan_forecast
| plan_id | account_code | fiscal_month | planned_amount | version_name |
|---|
| 11 | 4000 | 2024-01-01 | 1000.00 | Working |
| 12 | 4000 | 2024-02-01 | 1100.00 | Working |
| 13 | 5000 | 2024-01-01 | 800.00 | Working |
| 14 | 5000 | 2024-02-01 | 700.00 | Working |
| 15 | 6000 | 2024-02-01 | 0.00 | Working |
| 16 | 7000 | 2024-01-01 | 500.00 | Working |
| 17 | 8000 | 2024-03-01 | 300.00 | Working |
| 18 | 9100 | 2024-02-01 | 200.00 | Working |
Expected output
| account_code | fiscal_month | actual_amount | planned_amount | variance | reconciliation_status |
|---|
| 4000 | 2024-01-01 | 1000.00 | 1000.00 | 0.00 | matched |
| 4000 | 2024-02-01 | 1200.00 | 1100.00 | 100.00 | mismatch |
| 5000 | 2024-01-01 | 800.00 | 800.00 | 0.00 | matched |
| 5000 | 2024-02-01 | NULL | 700.00 | NULL | plan_only |
| 5000 | 2024-03-01 | 900.00 | NULL | NULL | actual_only |
| 6000 | 2024-02-01 | 0.00 | 0.00 | 0.00 | matched |
| 7000 | 2024-01-01 | 450.00 | 500.00 | -50.00 | mismatch |
| 8000 | 2024-03-01 | 300.00 | 300.00 | 0.00 | matched |
| 9000 | 2024-02-01 | 150.00 | NULL | NULL | actual_only |
