Your question is Reconcile ERP Actuals to Plan Variances. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
| Column | Type | Description |
|---|---|---|
| actual_idPK | INT | Primary key for the ERP posting |
| account_code | VARCHAR(20) | General ledger account code |
| fiscal_month | DATE | Month bucket for the actuals |
| actual_amount | DECIMAL(12,2) | Posted ERP amount |
| source_system | VARCHAR(50) | ERP source name |
| Column | Type | Description |
|---|---|---|
| plan_idPK | INT | Primary key for the plan row |
| account_code | VARCHAR(20) | Planning account code |
| fiscal_month | DATE | Month bucket for the plan |
| planned_amount | DECIMAL(12,2) | Planned amount from the planning tool |
| version_name | VARCHAR(50) | Plan version |