Write a SQL query to analyze production performance by plant, week, and product line, joining orders, inventory, and downtime tables to identify the biggest drivers of missed targets at Atlas Copco.
Return only plant and product-line weeks where produced units missed the target. Calculate inventory shortages and downtime impacts, identify the largest driver, and compare each shortfall with the previous week for that plant and product line.
plant, week_start, product_line, target_units, produced_units, shortfall_units, inventory_shortage_units, downtime_hours, primary_driver, plant_shortfall_rank, and shortfall_change_vs_prior_week.| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique production order identifier |
| plant | VARCHAR(100) | Atlas Copco production plant |
| order_date | DATE | Date associated with the production order |
| product_line | VARCHAR(100) | Product line being produced |
| target_units | INT | Units targeted for production |
| produced_units | INT | Units actually produced |
| Column | Type | Description |
|---|---|---|
| inventory_idPK | INT | Unique inventory snapshot identifier |
| plant | VARCHAR(100) | Plant holding the inventory |
| inventory_date | DATE | Date of the inventory snapshot |
| product_line | VARCHAR(100) | Product line requiring inventory |
| available_units | INT | Units available for production |
| required_units | INT | Units required for planned production |
| Column | Type | Description |
|---|---|---|
| downtime_idPK | INT | Unique downtime event identifier |
| plant | VARCHAR(100) | Plant experiencing downtime |
| downtime_date | DATE | Date of the downtime event |
| product_line | VARCHAR(100) | Product line affected by downtime |
| downtime_reason | VARCHAR(100) | Reason for the downtime event |
| downtime_hours | DECIMAL(8,2) | Duration of the downtime event in hours |