sccrbrg - 6 months ago 14

SQL Question

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))
```