Combining CASE Statements via SQL-CodePudding

I am trying to merge the below queries used for separate reports by combining the CASE statements. The JOIN information is the same, but the result of the WHERE statements also differs. For the Case_Type_ID, I need all records that are equal to both 1 and 2.

The queries are as follows:

Query 1:

SELECT
, XXXX
, (CASE WHEN question3.option_id = 3 THEN 'Symptomatic' ELSE CASE WHEN question3.option_id = 4 THEN 'Asymptomatic' ELSE NULL END END) AS SYMPTOM_STATUS

WHERE crr.case_Type_Id = 1 
GROUP BY (CASE WHEN question3.option_id = 3 THEN 'Symptomatic' ELSE CASE WHEN question3.option_id = 4 THEN 'Asymptomatic' ELSE NULL END END)
ORDER BY crr.QUARANTINE_END_DATE DESC

Query 2:

SELECT
, XXX
, (CASE WHEN question3.option_id = 17 THEN 'Symptomatic' ELSE CASE WHEN question3.option_id = 18 THEN 'Asymptomatic' ELSE NULL END END) AS SYMPTOM_STATUS

WHERE crr.case_Type_Id = 2 
GROUP BY (CASE WHEN question3.option_id = 17 THEN 'Symptomatic' ELSE CASE WHEN question3.option_id = 18 THEN
'Asymptomatic' ELSE NULL END END)
ORDER BY crr.QUARANTINE_END_DATE DESC"

CodePudding user response：

Without any other thing in your simplified code let's focus on your CASE expressions and the title of the question:
Your CASE

(CASE WHEN question3.option_id = 3 THEN 'Symptomatic' ELSE CASE WHEN question3.option_id = 4 THEN 'Asymptomatic' ELSE NULL END END) AS SYMPTOM_STATUS

If you put it this way (much more readable)

    (CASE 
        WHEN question3.option_id = 3 THEN 'Symptomatic' 
    ELSE 
        CASE 
            WHEN question3.option_id = 4 THEN 'Asymptomatic' 
        ELSE NULL 
        END 
    END) as SYMPTOM_STATUS

It is exactly the same but now it is ease to see that it could be written as one CASE expression with two WHEN conditions and make it a bit more simple. CASE expresions them selves does not need bracets to function as they already have strict sintax with CASE at start and END on the other side. So it could be like this:

    CASE 
        WHEN question3.option_id = 3 THEN 'Symptomatic' 
        WHEN question3.option_id = 4 THEN 'Asymptomatic' 
    ELSE 
        NULL 
    END as SYMPTOM_STATUS

Furthermore, if none of WHEN conditions is satisfied CASE will return Null, so in this example ELSE could be ommited:

    CASE 
        WHEN question3.option_id = 3 THEN 'Symptomatic' 
        WHEN question3.option_id = 4 THEN 'Asymptomatic' 
    END as SYMPTOM_STATUS

It looks ok now, simple and readable. The same thing done with your 2nd CASE expression would look like this:

    CASE 
        WHEN question3.option_id = 17 THEN 'Symptomatic' 
        WHEN question3.option_id = 18 THEN 'Asymptomatic' 
    END as SYMPTOM_STATUS

If you combine it with the previous one it says that if option_id is 3 or 17 then 'Symptomatic' and if 4 or 18 then Asymptomatic. Let's try to put it like this:

    CASE 
        WHEN question3.option_id = 3 OR question3.option_id = 17 THEN 'Symptomatic' 
        WHEN question3.option_id = 4 OR question3.option_id = 18 THEN 'Asymptomatic' 
    END as SYMPTOM_STATUS

Now you have them both combined and in one CASE expression with two WHEN conditions. This one should do the job as it is expected to. But (in this example data) it could be even more simplified if we put it like this:

    CASE 
        WHEN question3.option_id IN(3, 17) THEN 'Symptomatic' 
        WHEN question3.option_id IN(4, 18) THEN 'Asymptomatic' 
    END as SYMPTOM_STATUS

And that's it. If option_id is 3 or 17 the returned value is 'Symptomatic', if option_id is either 4 or 18 then 'Asymptomatic' and any other value of option_id will return Null.
Regards...