CASE in WHERE clause - retuns a single-row result instead of 4 rows?
The WHERE clause is used to filter data. You aren't trying to filter out the records, instead you want to include them but alter their displayed value based on their actual value. This is why the CASE statement in the SELECT statement gives you the desired results.

Specifically, the reason your query omits 3 results is not because the CASE statement isn't working, but rather that the first 2 WHEN statements will likely always evaluate false. Effectively, when Projects.StartDate &gt;= '2005/01/01' then Projects.[Name] would have to equal NULL for the records to be selected. If employee 24 had any projects started on or after 01/01/2005 and a NULL project name, those records would show up.

To break it down even further, for each record in your query, it will do the following.


Checks if Projects.StartDate = '2005-01-01 00:00:00.000'. If so, does Projects.[Name] = NULL. If so, return record.
If the check in step 1 evaluates false, then check if Projects.StartDate &gt; '2005-01-01 00:00:00.000'. If so, does Projects.[Name] = NULL. If so, return record.
If the checks in both step 1 and step 2 evaluate false, then does Projects.[Name] = Projects.[Name] then return record. Hint: it always will.


Because 3 of the records associated with employee 24 were on or after '2005-01-01 00:00:00.000' and did not have NULL project names, they were excluded from the results.