CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Michael A
Michael A

Reputation: 9900

CASE WHEN - What am I doing wrong?

I have the following (which I know is syntactically incorrect, maybe even bearing on ridiculous):

and lp.lease_current_stop_date
    case when @lease_expiry_period = 01
    then
        between dateadd(MONTH, +1,
                       getdate())
    else
        between dateadd(MONTH, +2,
                       getdate())

I'm just wondering what would be the best way to accomplish this? What I'm trying to do with the above is:

*When @lease_expiry_period is equal to 01 then show all leases expiring up to a month from now. When @lease_expiry_period is equal to 02 then show all leases expiring up to two months from now.*

Performance note

Worth noting that I didn't truncate the date in my example for simplicity also. As this is using month getdate() should be: 

Dateadd(d,0,datediff(d,0,getdate()))

For best performance.

Upvotes: 2

Views: 148

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

...
AND 'T' = CASE WHEN @lease_expiry_period IN (01, 02) THEN
             CASE WHEN lp.lease_current_stop_date 
                         BETWEEN getdate() 
                         AND dateadd(MONTH, @lease_expiry_period, getdate())                            
                  THEN 'T'
             END
          END
AND ...

Upvotes: 1

Daniel Powell
Daniel Powell

Reputation: 8293

How about the following

    select * 
    from [table] lp
    where  lp.lease_current_stop_date= (
    select case 
        when @lease_expiry_period = 01
        then
            dateadd(MONTH, +1, getdate())
        else
            dateadd(MONTH, +2,getdate())
        end)

Upvotes: 1

Francis Avila
Francis Avila

Reputation: 31621

AND lp.lease_current_stop_date BETWEEN getdate() AND dateadd(MONTH, @lease_expiry_period, getdate())

Upvotes: 6

Related Questions