CODEWITHSUNDEEP
sql-servercasewhere-clause
t25
t25

Reputation: 167

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.

Upvotes: 0

Views: 3438

Answers (3)

shawnt00
shawnt00

Reputation: 17915

I believe this is what you were intending to do although you'll probably stick with the accepted answer since that's the more familiar form. Obviously the trick below is to nest case expressions. Remember that case evaluates to a value, not a conditional, as many people have attempted to do.

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

And as was also hinted at by Gordon you could try:

...
where
    datediff(
        month,
        case when [days same form] then leasedate else rentlastchanged end,
        '2016-08-01 00:00:00'
    ) 
      >=
    case when [days same form] <= 9 then 8 else 12 end

There are some cases where these forms could prove useful. Most of the time I doubt it's a good idea.

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103467

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 
    ([days same form] > 9 and 
     datediff(month, rentlastchanged ,'2016-08-01 00:00:00') >= 12))

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can do what you want as a case statement, but it needs to be an argument to datediff():

create view dbo.eightmonthinc as
select *
from dbo.rentincreaseview 
where ([days same form] <= 9 and datediff(month, leasedate, '2016-08-01') >= 8
      ) or
      ([days same form] > 9 and datediff(month, rentlastchanged, '2016-08-01') >= 12
      );

The correct logic needs to repeat the comparison on [days same form] twice. In addition, you don't need the hh:mm:ss on the date constant.

Upvotes: 1

Related Questions