CODEWITHSUNDEEP
sqlsql-server
That Guy XY
That Guy XY

Reputation: 63

Multiple instances of the same ID populating in different columns because of the case statement

I'm getting the following results:

| Memb_i | p1 |  p2 |  p3 |
+--------+----+-----+-----+
|   2    | 0  |  1  |  0  |
|   2    | 1  |  0  |  0  |

when I run the below query:

SELECT DISTINCT 
ME1.MEMB_ID 
,CASE WHEN CL.CLAIM_ID > = 1 AND cl.PROC_CD = 'P1' THEN 1 else 0 END AS P1_flag 
,CASE WHEN CL.CLAIM_ID > = 1 AND cl.PROC_CD = 'P2' THEN 1 else 0 END AS P2_flag
,CASE WHEN CL.CLAIM_ID > = 1 AND cl.PROC_CD = 'P3' THEN 1 else 0 END AS P3_flag
,CASE WHEN CL.CLAIM_ID > = 1 AND cl.PROC_CD = 'P4' THEN 1 else 0 END AS P4_flag
FROM MEMBER_ELIGIBILITY ME1
LEFT OUTER JOIN CLAIM CL
ON CL.MEMB_ID = ME1.MEMB_ID

what I'd like to see is the following

| Memb_i | p1 | p2 | p3 |
+--------+----+----+----+
|   2    | 1  | 1  |  0 |

notice how member 2 has all the fields it has values in in a single row opposed to having them in multiple rows.

Upvotes: 2

Views: 80

Answers (2)

VBoka
VBoka

Reputation: 9083

Not sure what you need but here is one way to get it:

SELECT ME1.MEMB_ID 
,MAX(CASE WHEN CL.CLAIM_ID > = 1 AND cl.PROC_CD = 'P1' THEN 1 else 0 END) AS P1_flag 
,MAX(CASE WHEN CL.CLAIM_ID > = 1 AND cl.PROC_CD = 'P2' THEN 1 else 0 END) AS P2_flag
,MAX(CASE WHEN CL.CLAIM_ID > = 1 AND cl.PROC_CD = 'P3' THEN 1 else 0 END) AS P3_flag
,MAX(CASE WHEN CL.CLAIM_ID > = 1 AND cl.PROC_CD = 'P4' THEN 1 else 0 END) AS P4_flag
FROM MEMBER_ELIGIBILITY ME1
LEFT OUTER JOIN CLAIM CL
ON CL.MEMB_ID = ME1.MEMB_ID
GROUP BY ME1.MEMB_ID

Here is the DEMO

Upvotes: 2

rohitvats
rohitvats

Reputation: 1851

This should work:

Demo

select 
m.memb_id,
case when c.P1_total > 0 then 1 else 0 end P1_Flag,
case when c.P2_total > 0 then 1 else 0 end P2_Flag,
case when c.P3_total > 0 then 1 else 0 end P3_Flag
from member_eligibility m
left join (
  select 
  memb_id, 
  sum(case when proc_cd = 'P1' then 1 else 0 end) 'P1_total', 
  sum(case when proc_cd = 'P2' then 1 else 0 end) 'P2_total',
  sum(case when proc_cd = 'P3' then 1 else 0 end) 'P3_total'
  from claim
  group by memb_id
) c on c.memb_id = m.memb_id

Upvotes: 1

Related Questions