Using tsql greater than sign in CASE Expression

Question

(case [dbo].[YearsInService]([DateEngaged],getdate())
   when (0) then (0) 
   when (1) then (4) 
   when (2) then (8)
    when (3) then (12)
     when (4) then (32) 
     when (5) then (40) 
     when (6) then (48) 
     when (7) then (56) 
     when (8) then (104) 
     when (9) then (117) 
     when (10) then (150) else (-1) end)

Now in my the last line, how can i say that 10 and above should be returned as 150?

Answer 1

You can't, the CASE YourFunction WHEN ... is for equalities only. If you need to use "greater than", you'll need to rewrite your expression this way:

CASE WHEN [dbo].[YearsInService]([DateEngaged],getdate()) = 0 THEN 0
WHEN [dbo].[YearsInService]([DateEngaged],getdate()) = 1 THEN 4
WHEN.....
WHEN [dbo].[YearsInService]([DateEngaged],getdate()) >= 10 THEN 150 ELSE -1 END

Answer 2

You are using a Simple Case statement where logical expressions are not allowed. You need to use a Searched CASE expression. But in your case since you are using a function it will be bit costly to get the return value from the function for each expression.

Here is MSDN Link for both Simple Case and Searched CASE Syntax

I would suggest you to use a sub query with a Searched case as bellow.

select case when results = 0 then 0
            when results = 1 then 4
            ...
            when results >= 10 then 150
            else -1 end as CaseResults
from (select [dbo].[YearsInService]([DateEngaged],getdate()) results
      from yourTable
     ) Temp