Write a SQL query to analyze operational performance by site and month, joining work orders, staffing, and SLA tables to identify the biggest drivers of delay.
Return completed work orders that finished after their due time, classify SLA breaches using the applicable priority target, and include monthly staffing data when available.
site, month, delay_reason, delayed_orders, total_delay_hours, avg_delay_hours, sla_breach_rate, actual_staff_hours, driver_rank.site, month, and driver_rank ascending. Treat missing delay reasons as Unspecified.| Column | Type | Description |
|---|---|---|
| work_order_idPK | INT | Unique work order identifier |
| site | VARCHAR(100) | Operational site |
| opened_at | TIMESTAMP | Time the work order was opened |
| completed_at | TIMESTAMP | Completion time, null for open work orders |
| due_at | TIMESTAMP | Required completion time |
| priority | VARCHAR(20) | Priority used to find the SLA target |
| delay_reason | VARCHAR(100) | Reported primary cause of delay |
| Column | Type | Description |
|---|---|---|
| staffing_idPK | INT | Unique staffing record identifier |
| site | VARCHAR(100) | Operational site |
| month | DATE | First day of the staffing month |
| planned_staff_hours | NUMERIC(10,2) | Planned staffing hours |
| actual_staff_hours | NUMERIC(10,2) | Actual staffing hours worked |
| Column | Type | Description |
|---|---|---|
| priorityPK | VARCHAR(20) | Work order priority |
| target_hours | NUMERIC(10,2) | Maximum allowed completion duration in hours |