Easy SQL Incorrect Syntax when using > greater than

Question

This has been driving me crazy! It must be something simple.

Here is my code:

Select 
logid,
row_date,
sum(acdcalls) as 'total calls',
sum(ti_stafftime) as 'total time staffed',
sum(acdtime) as 'time on the phone',
Case acdtime
When acdtime > 0 Then
sum(ti_stafftime/acdtime)
Else '0'
End as MyPercent,
RepLName+', '+RepFName AS Agent,
SupLName+', '+SupFName AS Sup,
MgrLName+', '+MgrFName AS Manager

And I am getting the error message

Incorrect syntax near '>'.

What am I doing wrong here?

Answer 1

You have an item in the CASE portion and an expression in the WHEN. You must choose one or the other:

Case 
When acdtime > 0 Then sum(ti_stafftime/acdtime) 
Else '0'
End as MyPercent,

To use a value in the CASE portion you can do something like:

Case Foo
When 'Bar' Then 1
When 'Gamma' Then 2
...
End

However, you cannot do any complex logic in this scenario. I.e., it acts like a `switch' statement in many C-type languages by simply matching against the values in the When portions. When you want to do logic expressions, you need to keep the CASE portion blank and just have the WHEN portions.