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;