Home > Net >  Aggregating data by groups
Aggregating data by groups

Time:08-31

I have this table:

Location PT_ID Visit_DT Discharge_DT InjuryLevel InjuryCode Claim_ID Cost
Ab1 0001 01-01-2021 01-03-2021 7 I03 clm078 -400
Ab1 0001 01-01-2021 01-03-2021 1 I03 clm079 400
Ab1 0001 01-01-2021 01-03-2021 3 I03 clm068 500
Ab1 0001 01-01-2021 01-03-2021 3 I03 clm008 75
Ab2 0002 04-11-2021 04-12-2021 5 I03 clm111 1000
Ab2 0002 05-01-2021 05-03-2021 5 I03 clm176 900
Ab2 0002 08-08-2021 08-09-2021 6 I03 clm187 2000

Whats happening:

PT 001 visits the hospital on 01-01-2021 and there are three claims that occur on that day, all for the same visit with different injury codes recorded. I would like to pick the max injurylevel for that patient on that day (7) and indicate that they had 1 visit that was equal to InjuryLevel6to10. For patient 002, they have 3 different visits, 2 that fall under InjuryLevel1to5 and 1 in InjuryLevel6to10 (as shown below).

For both patients I would also like to add up their total cost.

Desired Output:

Location PT_ID InjuryLevel1to5 InjuryLevel6to10 TotalCost
Ab1 0001 0 1 575
Ab2 0002 2 1 3900

Any help would be appreciated

CodePudding user response:

Admittedly I'm not entirely sure of your criteria and your sample data needs a bit more variety to show each expected case.

However does the following work for you, or get you close?

select location, pt_id, 
  Sum(case when il between 1 and 5 then ct else 0 end) InjuryLevel1to5,
  Sum(case when il between 6 and 10 then ct else 0 end) InjuryLevel6to10,
  Sum(Cost) totalCost
from (
  select location, pt_id,Visit_DT, 
    Max(InjuryLevel) il, 
    Count(distinct Visit_DT) ct, 
    Sum(cost) cost
  from t
  group by location, pt_id,Visit_DT
)t
group by PT_ID, Location;
  • Related