SQL group by ID for multiple IDs using case when statements

I started with a list of patients with multiple codes (at multiple times of year) and need to split the patients into groups based on if they have a code or combo of codes and those that don’t qualify are excluded from the list. I have already created flags (0,1) for each set of codes. But the problem is that a patient can qualify or disqualify on another row. What I’d like is one row per patient which I can then determine the appropriate group per patient. Below is the two ways I’ve tried but I can’t figure out how to roll up by ID and/or the new column.

1st code I tried:

SELECT 
      a.*
    into file_2
      from (select code,ID, 'HL2' as grp_1
from file_1
where (code like '%I60.%' or code like '%I61.%')
  and (code not like '%I20.%' and code not like '%I21.%'
  and code not like '%I63.%' and code not like '%I64.%'
  and code not like '%I70.%') a

2nd code I tried:

,(case when (HL2='1' and dm='0' and ht='0') then 1 else 0 end) as exclude

HAVE

ID     CV  CA   HT  DM  HL  PA  HL1 HL2 exclude
1003    0   0   0   0   1   0   0   1   1   
1096    0   0   0   0   1   0   0   1   1   
1096    0   0   0   1   0   0   0   0   0   
1096    0   0   1   0   0   0   0   0   0   
1196    0   0   0   0   0   1   0   0   0   
1196    0   0   1   0   0   0   0   0   0   
1196    1   0   0   0   0   0   0   0   0   
1196    0   0   0   0   1   0   0   1   1

WANT

ID     CV  CA   HT  DM  HL  PA  HL1 HL2 exclude
1003    0   0   0   0   1   0   0   1   1   
1096    0   0   1   1   1   0   0   1   0   
1196    1   0   1   0   1   1   0   1   0

Answer

You seem to want conditional aggregation. Your question is a little hard to follow, but the idea is:

select id, max(cv) as cv,
       . . .
       (case when max(HL2) = 1 and max(dm) =  0 and max(ht) = 0) then 1 else 0 end) as exclude
from file_1
where (code like '%I60.%' or code like '%I61.%') and
      (code not like '%I20.%' and code not like '%I21.%' and
       code not like '%I63.%' and code not like '%I64.%' and
       code not like '%I70.%'
      )

Advertisement

Answer