Amulya Sharma Amulya Sharma - 2 months ago 15
SQL Question

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?

Answer

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.