Joining 3 Tables in One Select Statement

Question

I have this data set with patient information/charges (Table 1).

I am looking to achieve Table 3. Table 3 shown below has:

1. Distinct Patient Number (Patno)
2. The sum of Billed Charges for each Patno (sum billed charges then group by patno)
3. 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.
4. 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
5. 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

Table 2:

Table 3:

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.

Answer 1

Assume that each patient only has one DRG CODE. Try this:

SELECT t1.PATNO, 
    SUM([Billed Charges]) AS BilledCharges, 
    t2.[DRG Rate], 
    t2.[DRG CODE] AS [DRG],
    CASE WHEN SUM([Billed Charges]) > 275000 THEN (SUM([Billed Charges] - 275000) * 0.195 + t2.[DRG Rate] ELSE NULL END [Stop Loss],
    CASE WHEN SUM([Billed Charges]) > 275000 THEN (SUM([Billed Charges] - 275000) * 0.195 + t2.[DRG Rate] ELSE t2.[DRG Rate] END [Claim Total]
FROM Table1 AS t1 
INNER JOIN Table2 AS t2 ON t2.[DRG CODE] = t1.DRG
GROUP BY t1.PATNO, t2.[DRG Rate], t2.[DRG CODE];