I have this table:

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:

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;