sccrbrg sccrbrg - 5 months ago 7
SQL Question

Conditional 'Case When' within 'Where' Clause T-SQL

I'm trying to use a case when clause in a where statement to return values if they fall into 2 different categories.

This is my code so far:

create view dbo.eightmonthinc
as
select *
from dbo.rentincreaseview
where
case when [days same form] <= 9
then datediff(month, leasedate ,'2016-08-01 00:00:00') >= 8
else datediff(month, rentlastchanged ,'2016-08-01 00:00:00') >= 12)
end
go


Here is a verbal break down of what I am trying to do.

If my days same formula returns a value <= 9 then I want to only return values where the difference between the lease date and a set future date is >=8.

If my days same formula is >9 then I only want to return values where the difference between the rent last changed date and a future date is >=12.

However, I want both sets of data to be returned in the same query. Unfortunately, I keep getting an 'incorrect syntax' error.

I'm still learning, so I'm a little unsure of how to fix this. Any help would be greatly appreciated.

Answer

You can't use a case statement like that (evaluating different boolean expressions based on input), but you can rewrite your logic with boolean AND and OR instead:

where 
    ([days same form] <= 9 and 
        datediff(month, leasedate ,'2016-08-01 00:00:00') >= 8)
    or (datediff(month, rentlastchanged ,'2016-08-01 00:00:00') >= 12))