Keep Unassigned Employees in an As-Of Project Roster
Return every employee and any project assignment active on '2024-07-01'.
- Joins
- Date analysis
- NULL handling
- Sorting
Exercise brief
Understand the request
Portfolio operations manager A July 1 staffing snapshot must retain employees with no active project assignment.
Find pairs (employee, project_assignment) only when BOTH the employee_id matches AND the assignment is currently in effect (start_date <= '2024-06-30' AND (end_date IS NULL OR end_date >= '2024-06-30')). Return employee_id, first_name, last_name, project_id, allocation_pct, start_date, end_date — ordered by employee_id, project_id.
Return
- Return employee_id, first_name, last_name, project_id, allocation_pct, start_date, and end_date.
- Use NULL assignment fields for employees without an active assignment.
- Order by employee_id and project_id.
Constraints
- Use LEFT JOIN.
- Keep all assignment date predicates inside ON so unmatched employees survive.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
employee_idINTEGERfirst_nameVARCHAR(50)last_nameVARCHAR(50)
project_assignments
employee_idINTEGERproject_idINTEGERallocation_pctINTEGERstart_dateDATEend_dateDATE
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
A right-table filter in WHERE removes the NULL-padded rows produced by LEFT JOIN.
Hint 2
Put the employee key and both effective-date tests in the ON clause.
Hint 3
LEFT JOIN project_assignments pa ON e.employee_id = pa.employee_id AND pa.start_date <= '2024-07-01' AND (pa.end_date IS NULL OR pa.end_date >= '2024-07-01')
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT e.employee_id, e.first_name, e.last_name, pa.project_id, pa.allocation_pct, pa.start_date, pa.end_date FROM employees e LEFT JOIN project_assignments pa ON e.employee_id = pa.employee_id AND pa.start_date <= '2024-07-01' AND (pa.end_date IS NULL OR pa.end_date >= '2024-07-01') ORDER BY e.employee_id, pa.project_id;Why this works
Outer-join predicate placement is part of the result contract. Evaluating the date range in ON decides which assignments match while still emitting a NULL-padded row for every employee without a match.
Success check
All ten employees appear; employees 107, 108, and 109 have NULL assignment fields.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| employee_id | first_name | last_name | project_id | allocation_pct | start_date | end_date |
|---|---|---|---|---|---|---|
| 100 | John | Smith | 1 | 50 | 2024-01-01 | 2024-12-31 |
| 100 | John | Smith | 2 | 50 | 2024-01-01 | 2024-12-31 |
| 101 | Alice | Johnson | 1 | 80 | 2024-02-01 | 2024-11-30 |
| 102 | Bob | Wilson | 1 | 60 | 2024-02-15 | 2024-12-31 |
| 103 | Carol | Davis | 4 | 100 | 2024-04-01 | 2024-12-31 |
| 104 | David | Brown | 2 | 70 | 2024-03-01 | 2024-12-31 |
| 105 | Emma | Taylor | 1 | 30 | 2024-01-15 | 2024-12-31 |
| 105 | Emma | Taylor | 2 | 40 | 2024-01-15 | 2024-12-31 |
| 106 | Frank | Green | 2 | 100 | 2024-03-01 | 2024-12-31 |
| 107 | Grace | White | NULL | NULL | NULL | NULL |
Previewing 10 of 12 expected rows. Run the query in the editor to inspect the full result.
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
Self Joins & Hierarchical Queries
Query organization charts, trees, and parent-child relationships.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
Open the interactive workspace and practice across SQL topics.