SQL for Scheduled vs Actual Labor | Dataford Interview Questions

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.

Output

One row per shift and department.
Columns: shift_id, shift_date, department_name, shift_name, scheduled_hours, actual_hours, and variance_hours.
Calculate variance_hours as actual hours minus scheduled hours. Order by department name, shift date, shift name, and shift ID.

Schema

departments

Column	Type	Description
department_idPK	INT	Department identifier
department_name	VARCHAR(100)	Department name

scheduled_labor

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

actual_labor

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

Your question is SQL for Scheduled vs Actual Labor. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem