Fixing a Subquery

Question

I have this data set with patient information/charges [BCBS$]

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: [BCBS$]

Table 2: [BCDRG]

Table 3: Desired Output

My Code:

SELECT PATNO, SUM([AMT]) as AMT,  
(SELECT [DRG Rate] FROM [BCDRG] WHERE [DRG Code]=[BCBS$].[drg]) AS DRG_Rate,   
  
CASE WHEN SUM([AMT]) >275000 THEN ((SUM([AMT])-275000 )*.195)+DR.[DRG Rate] ELSE 'NO' END AS StopLoss,  
CASE WHEN SUM([AMT]) >275000 THEN ((SUM([AMT])-275000 )*.195)+DR.[DRG Rate] ELSE DR.[DRG Rate] END AS ClaimTotal  
  
FROM [BCBS$] AS RD  
INNER JOIN [BCDRG] AS DR ON RD.DRG=DR.[DRG Code]  
  
GROUP BY PATNO  

Error Message:

Msg 4104, Level 16, State 1, Line 30  
The multi-part identifier "BCBS$.drg" could not be bound.  

I feel I am really close to getting this to work. From my research it seems that this error is because SQL isn't able to locate this table. Any tips/advice would really be appreciated!

EDIT: So I noticed I aliased the table and that was an error. Fixed that and now I am getting Column 'BCDRG.DRG Rate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Is there a good way to get around this? I understand the error but I don't want to group by DRG rate or wrap in an aggregate, I just want the matching DRG Rate per unique patient.

My current code is :

SELECT PATNO, SUM([AMT]) as AMT,  
Dr.[DRG Rate],   
  
CASE WHEN SUM([AMT]) >275000 THEN ((SUM([AMT])-275000 )*.195)+DR.[DRG Rate] ELSE 'NO' END AS StopLoss,  
CASE WHEN SUM([AMT]) >275000 THEN ((SUM([AMT])-275000 )*.195)+DR.[DRG Rate] ELSE DR.[DRG Rate] END AS ClaimTotal  
  
FROM [BCBS$] AS RD  
INNER JOIN [BCDRG] AS DR ON RD.DRG=DR.[DRG Code]  
  
GROUP BY PATNO  
  

Answer 1

AS the error message mentioned, if you want to select the field which is not an aggregate function, i.e., SUM, COUNT, MAX, MIN and so on, you have to include it in the GROUP BY statement.

Is there a good way to get around this? I understand the error but I don't want to group by DRG rate or wrap in an aggregate, I just want the matching DRG Rate per unique patient.

I do not know why you do not want DRG Rate in the GROUP BY. You can try this:

;WITH CTE_PATNO_DRG AS (
    SELECT DISTINCT PATNO, DRG
    FROM BCBS$]
),
CTE_SUM_By_PATNO AS (
    SELECT PATNO, SUM(AMT) AS [AMT]
    FROM BCBS$
    GROUP BY PATNO
)

SELECT s.PATNO,
       CASE WHEN s.AMT > 275000 THEN CAST((s.AMT-275000)*.195+d.[DRG Rate] AS varchar(20)) ELSE 'NO' END AS StopLoss,
       CASE WHEN s.AMT > 275000 THEN (s.AMT-275000)*.195+DR.[DRG Rate] ELSE d.[DRG Rate] END AS ClaimTotal,
       d.[DRG Rate],
       d.[DRG Code] AS DRG,
FROM CTE_SUM_By_PATNO AS s
INNER JOIN CTE_PATNO_DRG AS d ON d.PATNO = s.PATNO

Please note in the first CASE statement, since (s.AMT-275000)*.195+DR.[DRG Rate] returns a decimal, you cannot use 'NO' in the ELSE. So you need to convert the decimal to varchar and then you can use 'NO' in the ELSE.