Write a SQL query to analyze operational performance by plant and month, joining production, inventory, and shipment tables to identify bottlenecks and late orders at TE Connectivity.
Use the provided production, inventory, and shipment tables. Return one row for every plant-month appearing in any table, including months without production.
plant_code, month, production totals, rejection rate, latest inventory quantities, shipment totals, late-order count, prior-month throughput change, and bottleneck_status.plant_code, then month ascending.| Column | Type | Description |
|---|---|---|
| production_idPK | INTEGER | Unique production record identifier |
| plant_code | VARCHAR(20) | TE Connectivity manufacturing plant code |
| production_date | DATE | Date of the production run |
| units_produced | INTEGER | Units completed during the run |
| units_rejected | INTEGER | Units rejected during quality inspection |
| Column | Type | Description |
|---|---|---|
| inventory_idPK | INTEGER | Unique inventory snapshot identifier |
| plant_code | VARCHAR(20) | TE Connectivity manufacturing plant code |
| snapshot_date | DATE | Date of the inventory snapshot |
| raw_material_qty | INTEGER | Available raw material quantity |
| finished_goods_qty | INTEGER | Available finished goods quantity |
| Column | Type | Description |
|---|---|---|
| shipment_idPK | INTEGER | Unique shipment identifier |
| plant_code | VARCHAR(20) | Plant responsible for the shipment |
| order_id | VARCHAR(30) | Customer order identifier |
| ship_date | DATE | Actual shipment date |
| promised_date | DATE | Promised shipment date |
| units_shipped | INTEGER | Units included in the shipment |