Using an aggregate function in an in line select statement

Question

T-SQL I am joining a services table to a payments table. I am using MAX, AVG for payments_rec'd and allowed_ amounts. I need Balance by last line number per service from the PayApp table. Sometimes payments are listed and then subtracted so the MIN balance doesn't work. I'm thinking of an inline select statement but cannot use an aggregate function inline.

SELECT DISTINCT [CDCLSVC].[RECNUM]       
,[CDCLSVC].[ID]       
,[CDCLSVC].[CLIENT_ID]       
,[CDCLIENT].[CASE_NUM]       
,[CDCLSVC].[UNIT_ID]       
,[CDCLSVC].[SUB_UNIT_ID]      
,[CDCLSVC].[SVC_ID]       
,[CDCLSVC].[EMP_ID]       
,[CDCLSVC].[BEG_DATE]       
,UPPER([CAEMP].[SORT_NAME]) as Employee       
,ISNULL([CDCLSVC].[EXTENDED_PRICE],0)  As ExtendedPrice       
,ISNULL([CDCLSVC].[BALANCE],0) As Balance         
,ISNULL(MAX([CDPAYAPP].[ALLOWED_AMT]),0) AS AllowedAmount
,ISNULL(MAX([CDPAYAPP].[ADJ_AMT]),0)As AdjustedAmount  
,ISNULL(SUM([CDPAYAPP].[PAY_APPLIED_AMT]),0)As PaymentApplied
,ISNULL(MAX([CDPAYAPP].[WRITE_OFF_AMT]),0)AS WriteOff  
,ISNULL(MIN([CDPAYAPP].[SVC_BALANCE]),0) AS SvcBalance  
,UPPER([CDPLACE].[DESC]) as Location    

FROM [AnaDEV].[dbo].[CDCLSVC]    
INNER JOIN [CAEMP]   ON [CDCLSVC].[EMP_ID] = [CAEMP].[ID]    
INNER JOIN [CDCLIENT]    ON [CDCLSVC].[CLIENT_ID] = [CDCLIENT].[ID]    
INNER JOIN [CDPLACE]   ON [CDCLSVC].[PLACE_ID] = [CDPLACE].[ID]    
INNER JOIN [CDPAYAPP]   ON [CDPAYAPP].[CLSVC_ID] = [CDCLSVC].[ID]   

WHERE [CDCLSVC].[BEG_DATE] between '2015-07-01' and DATEADD(DAY,- 
    (DATEPART(DAY, getdate())),GETDATE())

GROUP BY CDCLSVC.RECNUM,CDCLSVC.ID,CDCLSVC.CLIENT_ID,  
        CDCLIENT.CASE_NUM,CDCLSVC.UNIT_ID, CDCLSVC.SUB_UNIT_ID,
        CDCLSVC.SVC_ID,CDCLSVC.EMP_ID, CDCLSVC.BEG_DATE,    CAEMP.SORT_NAME,
        CDCLSVC.EXTENDED_PRICE,CDCLSVC.BALANCE, [CDPLACE].[DESC]   

ORDER BY [CDCLIENT].[CASE_NUM], [CDCLSVC].[ID]

Please provide us with more information -> table structure , input and expected output.. — sagi, Feb 16 '16 at 15:11
Possible duplicate of [Calculate running total / running balance](http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance) — Tab Alleman, Feb 16 '16 at 15:17

score 0 · Accepted Answer · answered Feb 19 '16 at 15:30

I have a table of services(CDCLSVC) for a small medical clinic. Each service has many payments/adjustments(PAYAPP). So I have a one to many relationship. I also have a look-up table of Clinicians and another for Clients. The goal is to group the services by Clinician, then Client and have one row per service. This was easy aggregating the PAYAPP table using SUM, MIN, MAX. The problem was the Balance in the PAYAPP table was like a checkbook and I just needed the last entry.

PAYAPP
ROWNUM  SVC    PRICE  ADJUSTMENT WRITE-OFF  PAYMENTS  BALANCE
1        001    $100       $25                           $75
2        001    $100                           $10       $65
3        001    $100                           $50       $15
4        001    $100                $15                  $0

EACH SERVICE MAX(PRICE) SUM(ADJUSTMENT) MAX(WRITE-OFF) SUM(PAYMENT)

The problem was getting the final BALANCE which I did by a sub query after the left outer join.

SELECT CDCLSVC.ID AS SERVICE_ID
      --,CDPAYAPP.[ID] AS CDPAYAPP_ID
      ,CDCLSVC.[CLIENT_ID]
      ,CDPAYAPP.[CLSVC_ID]
      ,ISNULL(CDCLSVC.[EXTENDED_PRICE],0) AS EXTENTED_PRICE
      ,ISNULL(CDCLSVC.[BALANCE],0) AS BALANCE
      ,ISNULL(MAX(CDPAYAPP.[ALLOWED_AMT]),0) AS AllowedAmt
      ,ISNULL(MAX(CDPAYAPP.[ADJ_AMT]),0) AS AdjAmt
      ,ISNULL(MAX(CDPAYAPP.[WRITE_OFF_AMT]),0)AS WriteOff
      ,ISNULL(SUM(CDPAYAPP.[PAY_APPLIED_AMT]),0)AS AppliedAmt
      ,ISNULL(TBL1.SVC_BALANCE,0) AS SVC_BALANCE
       ,CDCLSVC.[EMP_ID]
       ,CDCLSVC.[PLACE_ID]
       ,CDCLSVC.[BEG_DATE]
       ,CDCLSVC.[UNIT_ID]
       ,CDCLSVC.[SUB_UNIT_ID]
  FROM [AnaDEV].[dbo].[CDCLSVC]

  LEFT OUTER JOIN CDPAYAPP
  ON CDCLSVC.ID = CDPAYAPP.CLSVC_ID

 LEFT OUTER JOIN
            (SELECT SVC_BALANCE
            ,CLSVC_ID
            FROM CDPAYAPP
            WHERE CDPAYAPP.RECNUM IN 
                (SELECT MAX([RECNUM])
                  FROM [AnaDEV].[dbo].[CDPAYAPP]
                  GROUP BY CLSVC_ID)) AS TBL1

            ON TBL1.CLSVC_ID = CDPAYAPP.CLSVC_ID

    WHERE [CDCLSVC].[BEG_DATE] between '2015-07-01' and DATEADD(DAY,-(DATEPART(DAY, getdate())),GETDATE())
    GROUP BY CDCLSVC.ID,CDCLSVC.[CLIENT_ID],CDPAYAPP.CLSVC_ID,CDCLSVC.
            [EXTENDED_PRICE],CDCLSVC.[BALANCE],CDCLSVC.[EMP_ID] 
        ,CDCLSVC.[BEG_DATE],TBL1.SVC_BALANCE, CDCLSVC.PLACE_ID
         ,CDCLSVC.[UNIT_ID] ,CDCLSVC.[SUB_UNIT_ID]  
    ORDER BY CDCLSVC.BEG_DATE DESC   -- CHECKING THE WHERE CLAUSE

Using an aggregate function in an in line select statement

1 Answers1