Is it possible to use an AND operator in a case statement?

[Mike Chin]

Hi

I am trying to use an AND operator in a case statement but I keep on getting a error, is there an alternative way of doing this. The sample code is below

SUM( case  QUESTION_KEY when 17 and SCORE = 9 then 1 else 0 end) AS TOTAL_SURVEY
              
              from 
              
               FACT_NPS_ANSWER

Cheers

[NeoPa]

If you look at CASE (Transact-SQL) you'll see your example code is not in the correct format. You would need :

SELECT ...
     , SUM(CASE WHEN ([QUESTION_KEY] = 17)
                 AND ([SCORE] = 9)
           THEN 1 ELSE 0 END) AS [TOTAL_SURVEY]
 
FROM   [FACT_NPS_ANSWER]