CODEWITHSUNDEEP
sqlsql-serverjoinleft-joindistinct
shockwave
shockwave

Reputation: 3272

SQL Group rows for every ID using left outer join

I have a table with almost a million records of claims for 6 different conditions like Diabetes, Hypertension, Heart Failure etc. Every member has a number of claims. He might have claims with the condition as Diabetes or Hypertension or anything else. My goal is to group the conditions they have(number of claims) per every member row.

Existing table

+--------------+---------------+------+------------+
|  Conditions  | ConditionCode |  ID  | Member_Key |
+--------------+---------------+------+------------+
| DM           |          3001 | 1212 | A1528      |
| HTN          |          5001 | 1213 | A1528      |
| COPD         |          6001 | 1214 | A1528      |
| DM           |          3001 | 1215 | A1528      |
| CAD          |          8001 | 1823 | B4354      |
| HTN          |          5001 | 3458 | B4354      |
+--------------+---------------+------+------------+

Desired Result

+------------+------+-----+----+----+-----+-----+
| Member_Key | COPD | CAD | DM | HF | CHF | HTN |
+------------+------+-----+----+----+-----+-----+
| A1528      |    1 |     |  2 |    |     |   1 |
| B4354      |      |   1 |    |    |     |   1 |
+------------+------+-----+----+----+-----+-----+

Query

select distinct tr.Member_Key,C.COPD,D.CAD,DM.DM,HF.HF,CHF.CHF,HTN.HTN
   FROM myTable tr
   --COPD
  left outer join (select Member_Key,'X' as COPD
   FROM myTable
   where Condition=6001) C
  on C.Member_Key=tr.Member_Key
  --CAD
  left outer join ( ....

For now I'm just using 'X'. But i'm trying to get the number of claims in place of X based on condition. I don't think using a left outer join is efficient when you are searching 1 million rows and doing a distinct. Do you have any other approach in solving this

Upvotes: 0

Views: 71

Answers (3)

indiri
indiri

Reputation: 332

This is an ideal situation for CASE statments:

SELECT tr.Member_Key, 
    SUM(CASE WHEN Condition=6001 THEN 1 ELSE 0 END) as COPD,
    SUM(CASE WHEN Condition=6002 THEN 1 ELSE 0 END) as OtherIssue,
    SUM(CASE etc.)
FROM myTable tr
GROUP BY tr.Member_Key

Upvotes: 2

kjmerf
kjmerf

Reputation: 4345

This should be done with a PIVOT, like:

SELECT *
FROM
(SELECT conditions, member_key
 FROM t) src
 PIVOT
(COUNT (conditions)
 for conditions in ([COPD], [CAD], [DM], [HF], [CHF], [HTN])) pvt

Upvotes: 0

Hogan
Hogan

Reputation: 70538

You don't want so many sub-queries, this is easy with group by and case statements:

SELECT Member_Key
       SUM(CASE WHEN Condition=6001 THEN 1 ELSE 0 END) AS COPD,              
       SUM(CASE WHEN Condition=3001 THEN 1 ELSE 0 END) AS DM,
       SUM(CASE WHEN Condition=5001 THEN 1 ELSE 0 END) AS HTN,
       SUM(CASE WHEN Condition=8001 THEN 1 ELSE 0 END) AS CAD
FROM myTable
GROUP BY Member_Key

Upvotes: 4

Related Questions