Task
You are given monthly cost data for defense vehicle programs from two source systems: an ERP feed and a plant reporting feed. Write a PostgreSQL query that returns each program_code for March 2024 where the two sources do not reconcile. For each program, show the ERP total, the plant total, the absolute variance, and a status label of Missing Source, Mismatch, or Balanced.
Only include programs where at least one source has posted a non-zero amount for March 2024, and return only the rows that are not balanced. This tests how you would validate data integrity when combining multiple sources.
Schema
gdls_programs
| column | type | description |
|---|
| program_id | INT | Primary key for the program |
| program_code | VARCHAR(20) | Program identifier |
| platform_name | VARCHAR(100) | GDLS platform or vehicle name |
erp_costs
| column | type | description |
|---|
| cost_id | INT | Primary key for ERP cost row |
| program_id | INT | Program reference |
| posting_date | DATE | ERP posting date |
| cost_type | VARCHAR(30) | Cost category |
| amount | DECIMAL(12,2) | Posted amount |
plant_costs
| column | type | description |
|---|
| plant_entry_id | INT | Primary key for plant cost row |
| program_code | VARCHAR(20) | Program identifier from plant feed |
| report_month | DATE | Reporting month |
| amount | DECIMAL(12,2) | Reported amount |
Sample data
gdls_programs
| program_id | program_code | platform_name |
|---|
| 3 | ABRMS | Abrams SEPv3 |
| 1 | MPF | Griffin II |
| 2 | STRYKER | Stryker A1 |
erp_costs
| cost_id | program_id | posting_date | cost_type | amount |
|---|
| 101 | 1 | 2024-03-05 | labor | 12000.00 |
| 102 | 1 | 2024-03-18 | material | 8000.00 |
| 104 | 2 | 2024-03-09 | labor | 15000.00 |
plant_costs
| plant_entry_id | program_code | report_month | amount |
|---|
| 201 | MPF | 2024-03-01 | 20000.00 |
| 202 | STRYKER | 2024-03-01 | 14000.00 |
| 204 | ABRMS | 2024-03-01 | 5000.00 |
Expected output
| program_code | erp_total | plant_total | absolute_variance | reconciliation_status |
|---|
| ABRMS | 0.00 | 5000.00 | 5000.00 | Missing Source |
| STRYKER | 15000.00 | 14000.00 | 1000.00 | Mismatch |
