Best way to do nested case statement logic in SQL Server
I personally do it this way, keeping the embedded CASE expressions confined. I'd also put comments in to explain what is going on. If it is too complex, break it out into function.
SELECT
col1,
col2,
col3,
CASE WHEN condition THEN
CASE WHEN condition1 THEN
CASE WHEN condition2 THEN calculation1
ELSE calculation2 END
ELSE
CASE WHEN condition2 THEN calculation3
ELSE calculation4 END
END
ELSE CASE WHEN condition1 THEN
CASE WHEN condition2 THEN calculation5
ELSE calculation6 END
ELSE CASE WHEN condition2 THEN calculation7
ELSE calculation8 END
END AS 'calculatedcol1',
col4,
col5 -- etc
FROM table
You could try some sort of COALESCE trick, eg:
SELECT COALESCE( CASE WHEN condition1 THEN calculation1 ELSE NULL END, CASE WHEN condition2 THEN calculation2 ELSE NULL END, etc... )
Wrap all those cases into one.
SELECT
col1,
col2,
col3,
CASE
WHEN condition1 THEN calculation1
WHEN condition2 THEN calculation2
WHEN condition3 THEN calculation3
WHEN condition4 THEN calculation4
WHEN condition5 THEN calculation5
ELSE NULL
END AS 'calculatedcol1',
col4,
col5 -- etc
FROM table
You can combine multiple conditions to avoid the situation:
CASE WHEN condition1 = true AND condition2 = true THEN calculation1
WHEN condition1 = true AND condition2 = false THEN calculation2
ELSE 'what so ever' END,