Avoid using nested CASE statements in metrics. A CASE statement forces calculations to be done for every single attribute that meets that condition. Anything that does not match the CASE conditions reaches its ELSE clause and has to be processed as well.
CASE is used for complex conditional expressions that contain three or more conditions. Following the CASE keyword, conditions and outcomes follow the keywords WHEN and THEN, respectively. If none of the WHEN conditions are met, the outcome following ELSE is returned. Outcomes can be numerical values or arithmetic operations. All CASE expressions conclude with the END keyword.
SELECT CASE WHEN … THEN …, WHEN… THEN… ELSE… END
SELECT CASE WHEN condition1 THEN outcome1, WHEN condition2 THEN outcome2 ELSE outcome3 END
SELECT CASE WHEN activity_type IN(email, meeting) THEN 1, WHEN activity_type IN(phone_call, meeting) THEN 2 ELSE 0 END
SELECT CASE WHEN SUM(Amount) > SUM(Lost) AND SUM(Amount) – SUM(Lost) > 100000 THEN 2, WHEN SUM(Amount) > SUM(Lost) AND SUM(Amount) – SUM(Lost) < 100000 THEN 1 ELSE 0 END