In a T-SQL WHERE clause,
I want the
admit_date to be between
CI.end_date if there is a
CI.end_date. If there's not an end date, then I want
admit_date to be
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
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?
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);
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.
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)