SQL SP Error -

I am modifying an existing stored procedure someone else created.  I get an error when I run the query to save my sp.

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

The query that is causing the error is attached.  

The issue is with this line:
total_incurred  = SUM(CASE WHEN rt.ReserveTypeID in(SELECT Item FROM dbo.Split (@ReserveType, ',')) THEN r.ReserveAmount ELSE 0 END)

insert into #prtemp select
      r.claimantId,
      FY_process = ltrim(str((@lossfy -1)) + '/' + ltrim(str(@lossfy))), 
      total_paid = CAST(NULL AS DECIMAL(12, 2)) ,
      total_incurred  = SUM(CASE WHEN rt.ReserveTypeID in(SELECT Item FROM dbo.Split (@ReserveType, ',')) THEN r.ReserveAmount ELSE 0 END) 
FROM dbo.Reserve r ( NOLOCK ) 
         LEFT JOIN dbo.ReserveType rt ON r.ReserveTypeID = rt.ReserveTypeID
         where r.ClaimantID = @clmnt AND r.ProcessedDate <= ('6/30/'+ltrim(str(@lossfy)))   
GROUP BY r.ClaimantID

Open in new window

Scott WilliamsData AnalystAsked:
Who is Participating?
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
try

insert into #prtemp select select
      r.claimantId,
      FY_process = ltrim(str((@lossfy -1)) + '/' + ltrim(str(@lossfy))), 
      total_paid = CAST(NULL AS DECIMAL(12, 2)) ,
      total_incurred  = SUM(CASE WHEN charindex(',' + rt.ReserveTypeID + ',', ',' + @ReserveType+ ',') >0 THEN r.ReserveAmount ELSE 0 END) 
FROM dbo.Reserve r ( NOLOCK ) 
         LEFT JOIN dbo.ReserveType rt ON r.ReserveTypeID = rt.ReserveTypeID
         where r.ClaimantID = @clmnt AND r.ProcessedDate <= ('6/30/'+ltrim(str(@lossfy)))   
GROUP BY r.ClaimantID

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.