Aggregating data by groups

Question

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

Answer 1

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;