Hi all,
I have what I hope to be a simple question.
Say I have a data table;
| MEMBER_ID |
CLAIM_ID |
DIAGNOSIS |
COST |
| 1 |
154 |
Diabetes |
100 |
| 1 |
762 |
Infection |
150 |
| 1 |
612 |
Diabetes |
120 |
| 2 |
999 |
Flu |
50 |
| 2 |
211 |
Cataracts |
150 |
| 2 |
333 |
Diabetes |
50 |
-and I wish to get the resulting table;
| MEMBER_ID |
TOP_COSTING_DIAGNOSIS |
TOTAL_COST_OVERALL |
| 1 |
Diabetes |
370 |
| 2 |
Cataracts |
250 |
I'm having trouble getting the second column to return the highest costing diagnosis.
In this example, member 1 is showing diabetes because this member has two claims that total to 220 (100+120), which is higher than this member's other claim for infection (170).
I tried posting something a similar question a day ago that I believe just had too much going on.
I hope this is clearer on what I'm trying to ask for.
