Write a SQL query to analyze operational performance by joining personnel, schedule, and outcomes tables, and summarize key metrics by unit and month for US Army operations.
Calculate scheduled operations, reported outcomes, completed operations, completion rate, average task completion percentage, and total incidents. Exclude schedules without a date.
unit, operation_month, scheduled_operations, reported_outcomes, completed_operations, completion_rate_pct, avg_task_completion_pct, and total_incidents.| Column | Type | Description |
|---|---|---|
| personnel_idPK | INT | Unique personnel identifier |
| personnel_name | VARCHAR(100) | Service member name |
| unit | VARCHAR(100) | Assigned Army unit |
| role | VARCHAR(80) | Personnel duty role |
| Column | Type | Description |
|---|---|---|
| schedule_idPK | INT | Unique scheduled operation identifier |
| personnel_id | INT | Personnel member assigned to the schedule |
| scheduled_date | DATE | Scheduled operation date |
| mission_type | VARCHAR(80) | Type of scheduled operation |
| Column | Type | Description |
|---|---|---|
| outcome_idPK | INT | Unique outcome identifier |
| schedule_id | INT | Related scheduled operation |
| status | VARCHAR(30) | Reported operation status |
| tasks_completed | INT | Number of completed tasks |
| tasks_planned | INT | Number of planned tasks |
| incident_count | INT | Incidents recorded during the operation |