Are there any syntax errors in this case statement? Language = SQL Server

Question

case ('monthcharge' <> 0)
WHEN TRUE THEN 'monthcharge' * datediff(mm,, 'start', 'stop')
WHEN FALSE THEN case ('weekcharge' <>0)
                    WHEN TRUE THEN 'weekcharge' * datediff(ww, 'start', 'stop')
                    WHEN FALSE THEN 'daycharge' * datediff(dd, 'start', 'stop') 
                    ELSE 0
                    END
ELSE 0
END 'final_total'

Data types:

alias       | datatype
daycharge   | float
weekcharge  | float
monthcharge | float
start       | datetime
stop        | datetime

When you run it on SQL Server, does it say there are any syntax errors? This seems like a pretty easy to answer question. — dfundako, Nov 25 '19 at 16:14
`CASE` **expression**. And did you get any errors when you ran the query? if yes, then yes, if no, then no. — Thom A, Nov 25 '19 at 16:19

score 0 · Answer 1 · answered Nov 25 '19 at 16:17

The problem is you are putting your column names in quotes -- you want to take those quotes out.

Also you have to choices for your tests -- no need to case statements like that if you only are going to use two choices.

Here is the fixed code.

case WHEN monthcharge <> 0.0 TRUE THEN monthcharge * datediff(mm,, 'start', 'stop')
     ELSE case WHEN weekcharge <> 0.0 THEN weekcharge * datediff(ww, 'start', 'stop')
               ELSE daycharge * datediff(dd, 'start', 'stop') 
          END
END AS final_total

score 0 · Answer 2 · answered Nov 25 '19 at 16:30

Use single quotes for string constants. In case you have keywords as object names then use should use square brackets to escape them

Here is better version of what you are trying to do

SELECT final_total = CASE 
                       WHEN monthcharge <> 0 THEN monthcharge * Datediff(mm, [start], [stop]) 
                       WHEN weekcharge <> 0 THEN weekcharge * Datediff(ww, [start], [stop]) 
                       WHEN daycharge <> 0 THEN daycharge * Datediff(dd, [start], [stop]) 
                       ELSE 0 
                     END

Are there any syntax errors in this case statement? Language = SQL Server

2 Answers2