Excel IIF to SQL

Question

Post reply

Excel IIF to SQL

sharonsql2013

SSCertifiable

Points: 5908
More actions
April 3, 2014 at 1:18 pm

#396537

I need to create a query in SQL for an Excel statement which goes like ..
Check status , If its Yes then 'WithinStatus'
If it is not yes then check Hours and if Hours is < Benchmark then 'OutsideStatus' otherwise InsideStatus
I started with Case statement but then I am confused...
Case when Status = 'Yes' then 'WithinStatus'
Else
Case when Hours< Benchmark Then 'OutsideStatus'
Else
'WithinStatus'
End as StatusType.
Don't know how to go with the other Else....

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You don't need several CASE, one is enough.

Case when Status = 'Yes' then 'WithinStatus'

when Hours< Benchmark Then 'OutsideStatus'

Else 'WithinStatus'

End as StatusType.

If a condition evaluates to true, it won't evaluate the following possibilities.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Luis Cazares (4/3/2014)
You don't need several CASE, one is enough.
Case when Status = 'Yes' then 'WithinStatus'
when Hours< Benchmark Then 'OutsideStatus'
Else 'WithinStatus'
End as StatusType.
If a condition evaluates to true, it won't evaluate the following possibilities.

Luis is absolutely correct here. He made the same oversight that you did originally though. Your original post (although over complicated) would work. The last condition in both of our code should be 'InsideStatus'

Case when Status = 'Yes' then 'WithinStatus'

when Hours< Benchmark Then 'OutsideStatus'

Else 'InsideStatus'

End as StatusType

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/