Prevent CASE WHEN statements from Overwriting Previous Statements

Question

I have this SQL code here:

with first_cte as (
    select *,

        case 
            when var1  = 1 then 'a'
            when var1  = 0 then 'b'
            end as new_var_1,

        case
            when var3  > var2 then 'c'
            end as new_var_2,

        case
            when var5 > var2 then 'c' 
            end as new_var_3,

        case
            when var4 > var2 then 'c' 
            end as new_var_4

    from my_table
),   
second_cte as (

    select *,
        case 
            when new_var_2 = 'c' or new_var_3 = 'c' or new_var_4 = 'c' then 'c'
            else null
            end as new_var_5
    from first_cte
)

select *,
    case 
        when new_var_2 = 'c' or  new_var_3  = 'c' or new_var_4 = 'c' or new_var_5 = 'c' then 'c'
        else new_var_1
        end as final
from second_cte;

Here, I am using a series of CASE WHEN statements to identify certain conditions within my table. Finally, once all the conditions have been identified, I then combine them all into a new variable.

I tried to make it more efficient by including everything into a single CASE WHEN statement, but I was finding that some of the previous conditions were being overwritten. This new way ensured that nothing is being overwritten.

Is there still a way to make this code shorter while ensuring that no conditions are being overwritten?

Answer 1

Consider the following approaches to solving FizzBang with TSQL:

DECLARE @Table TABLE (ID INT);

;WITH cte AS (
SELECT 1 as ID
UNION ALL
SELECT ID+1
  FROM cte 
 WHERE ID < 21
)
INSERT INTO @Table (ID)
SELECT ID 
  FROM cte;
/* Method one */
SELECT ID, STRING_AGG(Fizz,', ') AS FizzBang
  FROM (
        SELECT ID, CASE WHEN ID % 3 = 0 THEN 'Fizz' END AS Fizz
          FROM @Table
        UNION ALL
        SELECT ID, CASE WHEN ID % 5 = 0 THEN 'Bang' END AS Bang
          FROM @Table
       ) a
 GROUP BY ID;
/* Method two */
SELECT ID, CASE WHEN ID % 3 = 0 THEN 'Fizz' ELSE '' END + CASE WHEN ID % 5 = 0 THEN 'Bang' ELSE '' END AS FizzBang
  FROM @Table;
/* Method three */
SELECT ID, CASE WHEN ID % 3 = 0 AND ID % 5 = 0 THEN 'Fizz, Bang'
                WHEN ID % 3 = 0 THEN 'Fizz' 
                WHEN ID % 5 = 0 THEN 'Bang'
            END AS FizzBang
  FROM @Table;

Method one: Leveraging the STRING_AGG function we're producing two result sets, one for fizz and one for bang, and aggregating them together. This makes the operation to combine the two case expressions fairly simple.

ID	FizzBang
1
2
3	Fizz
4
5	Bang
6	Fizz
7
8
9	Fizz
10	Bang
11
12	Fizz
13
14
15	Fizz, Bang
16
17
18	Fizz
19
20	Bang
21	Fizz

Method two: Here we use two separate case expressions which return a blank when no match is found and concatenate them together. This can become complicated when you want to use an optional separator ('Fizz, Bang' or 'Fizz' or 'Bang').

ID	FizzBang
1
2
3	Fizz
4
5	Bang
6	Fizz
7
8
9	Fizz
10	Bang
11
12	Fizz
13
14
15	FizzBang
16
17
18	Fizz
19
20	Bang
21	Fizz

Method three: This approach defines all three possible outcomes in the case expression from the get go. The case will always return the first true condition, so we have put 'Fizz, Bang' at the top, followed by 'Fizz' and then 'Bang'. Changing that order will result in 'Fizz, Bang' never returning.

ID	FizzBang
1
2
3	Fizz
4
5	Bang
6	Fizz
7
8
9	Fizz
10	Bang
11
12	Fizz
13
14
15	Fizz, Bang
16
17
18	Fizz
19
20	Bang
21	Fizz