所以我被困在我的声明中。每个个案在where子句中运行都很好,但是当我尝试将其包装起来时,它就对我不利。在when语句中使用between语句是否存在问题?下面是代码。如果有什么想法请分享!
英文的逻辑是在ssrs表单上有一个复选框。所以box可以是1或0,根据值,我将执行where语句。
WHERE (CASE @box
WHEN 1 THEN (CheckReg.CheckDate BETWEEN @ChkStartDate and @ChkEndDate OR CheckReg.CheckDate IS NULL)
ELSE 0 THEN (CheckReg.CheckDate BETWEEN @ChkStartDate and @ChkEndDate OR CheckReg.CheckDate IS NULL
AND NOT CheckReg.CheckDate IS NULL AND CheckReg.CheckDate IS NOT NULL)
END)
另外,如果有人知道更好的解决方案,那么请根据我的逻辑告诉我。我正在使用Microsoft sql服务器
您需要使用 OR
WHERE
(@box = 1 AND (CheckReg.CheckDate BETWEEN @ChkStartDate and @ChkEndDate OR CheckReg.CheckDate IS NULL))
OR
(@box = 0 AND (CheckReg.CheckDate BETWEEN @ChkStartDate and @ChkEndDate OR CheckReg.CheckDate IS NULL
AND NOT CheckReg.CheckDate IS NULL AND CheckReg.CheckDate IS NOT NULL))
或者您可以WHEN
部分组合条件并与进行比较THEN
:
WHERE
(CASE
WHEN @box = 1 AND (CheckReg.CheckDate BETWEEN @ChkStartDate and @ChkEndDate OR CheckReg.CheckDate IS NULL)
THEN 1
WHEN @box = 0 AND (CheckReg.CheckDate BETWEEN @ChkStartDate and @ChkEndDate OR CheckReg.CheckDate IS NULL
AND NOT CheckReg.CheckDate IS NULL AND CheckReg.CheckDate IS NOT NULL)
THEN 1
ELSE 0
END
) = 1
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句