CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012case
AS91
AS91

Reputation: 527

Implement CASE in WHERE clause

In a T-SQL WHERE clause,

I want the admit_date to be between CI.start_date and CI.end_date if there is a CI.end_date. If there's not an end date, then I want admit_date to be >= CI.start_date

   WHERE 1=1
       AND CASE WHEN CI.end_date IS NULL 
                 THEN CEV.admit_date >= CI.start_date
                 ELSE CEV.admit_date BETWEEN CI.start_date AND CI.end_date 
                END

This will not work since I'm unable to make this CASE a part of an expression in the WHERE clause and not the expression itself.

How do I implement this?

Upvotes: 0

Views: 108

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

CASE doesn't belong in the WHERE clause. It exists to give you the opportunity to evaluate boolean expressions. In the WHERE clause you don't need it, because the clause is a boolean expression itself.

WHERE (CI.end_date IS NULL AND CEV.admit_date >= CI.start_date)
   OR (CI.end_date IS NOT NULL AND CEV.admit_date BETWEEN CI.start_date AND CI.end_date);

Or:

WHERE CEV.admit_date >= CI.start_date)
  AND (CEV.admit_date <= CI.end_date OR CI.end_date IS NULL);

This can be replaced with

WHERE CEV.admit_date BETWEEN CI.start_date AND COALESCE(CI.end_date, CEV.admit_date);

by the way.

Upvotes: 3

juergen d
juergen d

Reputation: 204766

WHERE (CI.end_date IS NULL AND CEV.admit_date >= CI.start_date)
   OR (CEV.admit_date BETWEEN CI.start_date AND CI.end_date)

Upvotes: 0

Related Questions