Write a SQL query to compare labor hours scheduled versus labor hours actually worked by shift and department.
Use the provided scheduling, timekeeping, and department data. Include shifts that appear in only one labor-hours source, treating missing hours as zero.
shift_id, shift_date, department_name, shift_name, scheduled_hours, actual_hours, and variance_hours.variance_hours as actual hours minus scheduled hours. Order by department name, shift date, shift name, and shift ID.| Column | Type | Description |
|---|---|---|
| department_idPK | INT | Department identifier |
| department_name | VARCHAR(100) | Department name |
| Column | Type | Description |
|---|---|---|
| shift_idPK | INT | Scheduled shift identifier |
| shift_date | DATE | Date of the scheduled shift |
| department_id | INT | Scheduled shift department |
| shift_name | VARCHAR(50) | Shift label |
| scheduled_hours | NUMERIC(6,2) | Hours planned for the shift |
| Column | Type | Description |
|---|---|---|
| work_idPK | INT | Actual labor record identifier |
| shift_id | INT | Shift identifier from timekeeping |
| shift_date | DATE | Date of the worked shift |
| department_id | INT | Worked shift department |
| shift_name | VARCHAR(50) | Shift label from timekeeping |
| actual_hours | NUMERIC(6,2) | Hours actually worked |