问题描述:

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?

网友答案:

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.

网友答案:
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)
相关阅读:
Top