Complex case statement using group

I have the following table:

tbl

ID  vaccine_code     vaccine_day
A   1A               0
A   2A               30
B   moderna,1A       0
B   moderna,2A       35
C   moderna          0
C   moderna          25

there are various ways in which the vaccines are coded in my database. 1A denotes first dose and 2A denotes second dose. At times, as in ID='C', the dose number is not denoted. In that case, I need to use the value in the vaccine_day column to extrapolate dose information.

I am looking for the following:

ID  vaccine_dose   vaccine_day
A   dose1          0
A   dose2          30
B   dose1          5
B   dose2          35
C   dose1          0
C   dose2          25

So far, I have:

select ID,
       case when vaccine_code like '%1A%' then 'dose1'
       case when vaccine_code like '%2A%' then 'dose2'
       case when vaccine_code = 'moderna' and min(vaccine_day) then 'dose1'
       case when vaccine_code = 'moderna' and max(vaccine_day) then 'dose1'
from tbl
group by vaccine_day;

>Solution :

You have a few issues with your query.

1. You don’t end your case.

2. You group by vaccine_day which will return you each vaccine_day.

Didn’t test it as I don’t have the full sample data, both your sample data are the same id and days, but you can start with something like this:

select id, case when vaccine_code like '%1A%' then 'dose1'
            when vaccine_code like '%2A%' then 'dose2'
            when vaccine_code = 'moderna' and vaccine_day = min(vaccine_day) then 'dose1'
            when vaccine_code = 'moderna' and vaccine_day = max(vaccine_day) then 'dose1'
       end as vaccine_dose
from tbl
group by id, vaccine_code;