Write a SQL query to analyze staffing performance by site, date, and shift, including joins across staffing, schedules, and attendance tables.
Use schedules as the required shift-level grain and analyze dates from 2025-06-01 through 2025-06-03. Include shifts without staffing records and calculate staffing fill and attendance rates.
site_name, work_date, shift, scheduled_headcount, staffed_count, attended_count, fill_rate_pct, attendance_rate_pctsite_name, work_date, and shift ascending| Column | Type | Description |
|---|---|---|
| schedule_idPK | INT | Unique scheduled shift identifier |
| site_name | VARCHAR(100) | Delaware Staffing client site |
| work_date | DATE | Date of the scheduled shift |
| shift | VARCHAR(20) | Shift label |
| scheduled_headcount | INT | Number of workers requested for the shift |
| Column | Type | Description |
|---|---|---|
| staffing_idPK | INT | Unique staffing assignment identifier |
| schedule_id | INT | Scheduled shift receiving the assignment |
| worker_id | VARCHAR(20) | Assigned worker identifier |
| assignment_status | VARCHAR(20) | Assignment state |
| Column | Type | Description |
|---|---|---|
| attendance_idPK | INT | Unique attendance record identifier |
| schedule_id | INT | Scheduled shift associated with attendance |
| worker_id | VARCHAR(20) | Worker whose attendance was recorded |
| attendance_status | VARCHAR(20) | Attendance outcome |