Use CASE statement results in WHERE

BobRosas
BobRosas used Ask the Experts™
on
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
FROM            vw_HrIndividuals
WHERE        (@Date BETWEEN CONVERT(datetime, CONVERT(char(8), LastStartDate, 112)) AND '4/2/12')
GROUP BY DeptID
ORDER BY DeptID
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
BI Consultant
Most Valuable Expert 2011
Commented:
Your case statement does not return any value when it's not NULL, is that the intention?  I have the impression you forgot the ELSE part to return MAX(vw_HrIndividuals.LastTermDate)

Here's a simpler expression to achieve the same result (assuming you'd like the MAX of your LastTermDate returned when it exists):

COALESCE(MAX(vw_HrIndividuals.LastTermDate), GETDATE())

It can also be used in the WHERE:

SELECT        COUNT(DeptID) AS Count, DeptID
      , COALESCE(MAX(vw_HrIndividuals.LastTermDate), GETDATE()) as EndDate
FROM            vw_HrIndividuals
WHERE        @Date BETWEEN CONVERT(datetime, CONVERT(char(8), LastStartDate, 112))
      AND COALESCE(MAX(vw_HrIndividuals.LastTermDate), GETDATE())
GROUP BY DeptID
ORDER BY DeptID

Author

Commented:
I actually didn't need the MAX.  I only put it in because I did not want to group by the date.  But I took it out and it works GREAT!  Thank you so much!

Author

Commented:
Thank you so much!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial