Context
VLink's employee directory needs to identify active employees who are not currently assigned to any department. Department assignments may be expired, cancelled, inactive, or scheduled for a future date.
Task
Write a PostgreSQL query using an as-of date of 2025-02-15 to find all active VLink employees without a valid current department assignment.
Requirements
- Consider an assignment current only when its status is
ACTIVE, its start date is on or before the as-of date, and its end date is null or on or after the as-of date.
- Return each qualifying employee once, even if assignment history contains multiple rows.
- Include the employee's most recent recorded department assignment, department hierarchy path, and assignment end date when available.
- Exclude employees whose employment status is not
ACTIVE.
- Use the department hierarchy to produce a readable path from the top-level department to the assigned department.
- Sort the results by employee name.
