The following code is giving me the results I want except i have the date hard coded in the WHERE clause because the result of my CASE statement doesn't work. How do I substitute '4/2/12' with EndDate so it's not a static value?
SELECT COUNT(DeptID) AS Count, DeptID, CASE WHEN MAX(vw_HrIndividuals.LastTermDate) IS NULL THEN GetDate() END AS EndDate
WHERE (@Date BETWEEN CONVERT(datetime, CONVERT(char(8), LastStartDate, 112)) AND '4/2/12')
GROUP BY DeptID
ORDER BY DeptID