Write a SQL query to analyze operational performance by team and month, joining delivery data to staffing data and calculating utilization and variance to target.
Use the provided delivery and staffing tables. Include delivery months even when staffing data is unavailable, and avoid division by zero.
team_id, month_start, delivered_units, actual_hours, available_hours, utilization_pct, target_utilization_pct, and variance_to_target_pct.team_id, then month_start.| Column | Type | Description |
|---|---|---|
| delivery_idPK | INT | Unique delivery record identifier |
| team_id | VARCHAR(20) | Identifier of the delivery team |
| delivery_date | DATE | Date on which delivery work was recorded |
| delivered_units | INT | Number of units delivered |
| actual_hours | DECIMAL(10,2) | Hours spent on delivery work |
| Column | Type | Description |
|---|---|---|
| staffing_idPK | INT | Unique staffing record identifier |
| team_id | VARCHAR(20) | Identifier of the staffed team |
| month_start | DATE | First day of the staffing month |
| available_hours | DECIMAL(10,2) | Hours available for delivery work |
| target_utilization_pct | DECIMAL(5,2) | Target utilization percentage for the team and month |