I have this data set with patient information/charges (Table 1).
I am looking to achieve Table 3. Table 3 shown below has:
Distinct Patient Number (Patno)
The sum of Billed Charges for each Patno (sum billed charges then group by patno)
DRG Rate from Table 2. This is just matching the DRG in Table 1 to Table 2 and getting the DRG Rate Associated with it.
Stop loss column. If the sum of Billed Charges for a patient (found in column 2) is >275000 then (SUM of Billed Charges -275000)*.195+ DRG Rate from column 3
Claim total: If there is a stop loss, then the claim total is just = to the stop loss value found in column 4. ELSE, it is equal to the DRG rate.
Note, each patient has only ONE claim, but can have multiple line items as shown in table 1.
Table 1: Raw Data
Please let me know if you need any additional info. I know this is a lot to ask, honestly any tips/tricks advice will really help ! Thanks in advance ! If you need me to give INSERT/CREATE TABLE statements to mess around with it just let me know.