question

AmeryHisham-2893 avatar image
0 Votes"
AmeryHisham-2893 asked ·

Joining 3 Tables in One Select Statement

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

74412-image.png

Table 2:

74388-image.png

Table 3:

74374-image.png

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.







sql-server-general
image.png (25.5 KiB)
image.png (14.9 KiB)
image.png (17.6 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

GuoxiongYuan-7218 avatar image
1 Vote"
GuoxiongYuan-7218 answered ·

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];
· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks! Much appreciated

0 Votes 0 ·