In the attached Stored Procedure I need to sum [Indem Paid] and [EXP Paid] by CSC.DispositionCode. For example if the Disposition Code = 3 I want one record that has the sum of records with the Disposition Code = 3. I will need this for all the codes that O have listed in my query. You will see I have commented out code that I attempted to use without success.

USE [EXPORTpahpixPROD]GO/****** Object: StoredProcedure [dbo].[PXQ7070A] Script Date: 09/22/2011 07:53:59 ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGO/* ***************************************************************************************************//* Closed Claims Report *//* ***************************************************************************************************/ALTER PROCEDURE [dbo].[PXQ7070A]( @FromDate DATETIME , @ToDate DATETIME, @GblOptState VARCHAR(4))AS SET NOCOUNT ON DECLARE @GetMonth VARCHAR(2) DECLARE @GetYear VARCHAR(4) SET @GetMonth = MONTH(GETDATE()) SET @GetYear = YEAR(GETDATE())SELECT DISTINCT [Category] = CASE WHEN CSC.DispositionCode = '5' THEN 'Trial' WHEN CSC.DispositionCode = '6' THEN 'Trial' WHEN CSC.DispositionCode = ' ' AND AppealFlag = 'Y' THEN 'Trial' WHEN CSC.DispositionCode = '11' THEN 'Trial' WHEN CSC.DispositionCode = '12' THEN 'Trial' WHEN CSC.DispositionCode = '13' THEN 'Arbitration' WHEN CSC.DispositionCode = '14' THEN 'Arbitration' WHEN CSC.DispositionCode = '15' THEN 'Mediation' WHEN CSC.DispositionCode = '18' THEN 'Early Offer Claims' WHEN CSC.DispositionCode = '4' THEN 'Involuntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' THEN 'Voluntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' THEN 'Settlements' WHEN CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' THEN 'Incident Only Payment' END ,[Type] = CASE WHEN CSC.DispositionCode = '5' THEN 'Judgment for Plaintiff' WHEN CSC.DispositionCode = '6' THEN 'Judgment for Defendant' WHEN CSC.DispositionCode = ' ' AND AppealFlag = 'Y' THEN 'Pending Appeal' WHEN CSC.DispositionCode = '11' THEN 'For Plaintiff After Appeal' WHEN CSC.DispositionCode = '12' THEN 'For Defendant After Appeal' WHEN CSC.DispositionCode = '13' THEN 'Award for Plaintiff' WHEN CSC.DispositionCode = '14' THEN 'Award for Defense' WHEN CSC.DispositionCode = '15' THEN 'Mediation' WHEN CSC.DispositionCode = '18' THEN 'Early Offer Claims' WHEN CSC.DispositionCode = '4' THEN 'Involuntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' THEN 'Voluntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' THEN 'Settlements' WHEN CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' THEN 'Incident Only Payment' END ,LEFT(Claim, 9) AS Claim ,Insured ,CSC.DispositionCode ,[Indem Paid] ,[Exp Paid]-- ,SumJudgmentforPlaintiff = -- CASE -- WHEN CSC.DispositionCode = '5' THEN CONVERT(money, (SUM([Indem Paid])))-- ELSE 0-- END-- ,SumJudgmentforDefendant = -- CASE -- WHEN CSC.DispositionCode = '6' THEN CONVERT(money, (SUM([Indem Paid])))-- ELSE 0-- END-- ,SumInvoluntaryDismissal = -- CASE -- WHEN CSC.DispositionCode = '3' THEN CONVERT(money, (SUM([Indem Paid])))-- ELSE 0-- ENDFROM EXPORTpahpixPROD.dbo.PXQ7000_UDF(@GetMonth, @GetYear, NULL, 0, 0, 0, 1, 0, '99-') INNER JOIN wellandp.ClaimSubclaim CSC ON dbo.PXQ7000_UDF.Claim = CSC.ClaimNoWHERE [Disposition Date] BETWEEN '01/01/2011' AND '09/01/2011' AND ( CSC.DispositionCode IN ( '4', '5', '6', '11', '12', '13', '14', '15', '18' ) OR ( CSC.DispositionCode = '3' AND [Indem Paid] >= '0.00' ) OR ( CSC.DispositionCode = 'E' AND AppealFlag = 'Y' ) ) AND (CurrentFlag = 'Y')GROUP BY CSC.DispositionCode ,AppealFlag ,[Indem Paid] ,Claim ,Insured ,[Exp Paid]ORDER BY CSC.DispositionCode

USE [EXPORTpahpixPROD]GO/****** Object: StoredProcedure [dbo].[PXQ7070A] Script Date: 09/22/2011 07:53:59 ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGO/* ***************************************************************************************************//* Closed Claims Report *//* ***************************************************************************************************/ALTER PROCEDURE [dbo].[PXQ7070A]( @FromDate DATETIME , @ToDate DATETIME, @GblOptState VARCHAR(4))AS SET NOCOUNT ON DECLARE @GetMonth VARCHAR(2) DECLARE @GetYear VARCHAR(4) SET @GetMonth = MONTH(GETDATE()) SET @GetYear = YEAR(GETDATE())SELECT [Type], SUM([Indem Paid]) AS [Indem Paid]FROM ( SELECT CASE WHEN CSC.DispositionCode = '5' THEN 'Judgment for Plaintiff' WHEN CSC.DispositionCode = '6' THEN 'Judgment for Defendant' WHEN CSC.DispositionCode = ' ' AND AppealFlag = 'Y' THEN 'Pending Appeal' WHEN CSC.DispositionCode = '11' THEN 'For Plaintiff After Appeal' WHEN CSC.DispositionCode = '12' THEN 'For Defendant After Appeal' WHEN CSC.DispositionCode = '13' THEN 'Award for Plaintiff' WHEN CSC.DispositionCode = '14' THEN 'Award for Defense' WHEN CSC.DispositionCode = '15' THEN 'Mediation' WHEN CSC.DispositionCode = '18' THEN 'Early Offer Claims' WHEN CSC.DispositionCode = '4' THEN 'Involuntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' THEN 'Voluntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' THEN 'Settlements' WHEN CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' THEN 'Incident Only Payment' END AS [Type], [Indem Paid] FROM EXPORTpahpixPROD.dbo.PXQ7000_UDF(@GetMonth, @GetYear, NULL, 0, 0, 0, 1, 0, '99-') INNER JOIN wellandp.ClaimSubclaim CSC ON dbo.PXQ7000_UDF.Claim = CSC.ClaimNo WHERE [Disposition Date] BETWEEN '01/01/2011' AND '09/01/2011' AND ( CSC.DispositionCode IN ( '4', '5', '6', '11', '12', '13', '14', '15', '18' ) OR ( CSC.DispositionCode = '3' AND [Indem Paid] >= '0.00' ) OR ( CSC.DispositionCode = 'E' AND AppealFlag = 'Y' ) ) AND (CurrentFlag = 'Y') ) tmpGROUP BY [Type]ORDER BY [Type]

USE [EXPORTpahpixPROD]GO/****** Object: StoredProcedure [dbo].[PXQ7070A] Script Date: 09/22/2011 07:53:59 ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGO/* ***************************************************************************************************//* Closed Claims Report *//* ***************************************************************************************************/ALTER PROCEDURE [dbo].[PXQ7070A]( @FromDate DATETIME , @ToDate DATETIME, @GblOptState VARCHAR(4))AS SET NOCOUNT ON DECLARE @GetMonth VARCHAR(2) DECLARE @GetYear VARCHAR(4) SET @GetMonth = MONTH(GETDATE()) SET @GetYear = YEAR(GETDATE())SELECT DISTINCT [Category] = CASE WHEN CSC.DispositionCode = '5' THEN 'Trial' WHEN CSC.DispositionCode = '6' THEN 'Trial' WHEN CSC.DispositionCode = ' ' AND AppealFlag = 'Y' THEN 'Trial' WHEN CSC.DispositionCode = '11' THEN 'Trial' WHEN CSC.DispositionCode = '12' THEN 'Trial' WHEN CSC.DispositionCode = '13' THEN 'Arbitration' WHEN CSC.DispositionCode = '14' THEN 'Arbitration' WHEN CSC.DispositionCode = '15' THEN 'Mediation' WHEN CSC.DispositionCode = '18' THEN 'Early Offer Claims' WHEN CSC.DispositionCode = '4' THEN 'Involuntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' THEN 'Voluntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' THEN 'Settlements' WHEN CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' THEN 'Incident Only Payment' END ,[Type] = CASE WHEN CSC.DispositionCode = '5' THEN 'Judgment for Plaintiff' WHEN CSC.DispositionCode = '6' THEN 'Judgment for Defendant' WHEN CSC.DispositionCode = ' ' AND AppealFlag = 'Y' THEN 'Pending Appeal' WHEN CSC.DispositionCode = '11' THEN 'For Plaintiff After Appeal' WHEN CSC.DispositionCode = '12' THEN 'For Defendant After Appeal' WHEN CSC.DispositionCode = '13' THEN 'Award for Plaintiff' WHEN CSC.DispositionCode = '14' THEN 'Award for Defense' WHEN CSC.DispositionCode = '15' THEN 'Mediation' WHEN CSC.DispositionCode = '18' THEN 'Early Offer Claims' WHEN CSC.DispositionCode = '4' THEN 'Involuntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' THEN 'Voluntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' THEN 'Settlements' WHEN CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' THEN 'Incident Only Payment' END ,LEFT(Claim, 9) AS Claim ,Insured ,CSC.DispositionCode ,[Indem Paid] ,[Exp Paid] ,SUM(CASE WHEN CSC.DispositionCode = '5' THEN CONVERT(money, (SUM([Indem Paid]))) ELSE 0 END) AS SumJudgmentforPlaintiff ,SUM(CASE WHEN CSC.DispositionCode = '6' THEN CONVERT(money, (SUM([Indem Paid]))) ELSE 0 END) AS SumJudgmentforDefendant ,SUM(CASE WHEN CSC.DispositionCode = '3' THEN CONVERT(money, (SUM([Indem Paid]))) ELSE 0 END) AS SumInvoluntaryDismissalFROM EXPORTpahpixPROD.dbo.PXQ7000_UDF(@GetMonth, @GetYear, NULL, 0, 0, 0, 1, 0, '99-') INNER JOIN wellandp.ClaimSubclaim CSC ON dbo.PXQ7000_UDF.Claim = CSC.ClaimNoWHERE [Disposition Date] BETWEEN '01/01/2011' AND '09/01/2011' AND ( CSC.DispositionCode IN ( '4', '5', '6', '11', '12', '13', '14', '15', '18' ) OR ( CSC.DispositionCode = '3' AND [Indem Paid] >= '0.00' ) OR ( CSC.DispositionCode = 'E' AND AppealFlag = 'Y' ) ) AND (CurrentFlag = 'Y')GROUP BY CSC.DispositionCode ,AppealFlag ,[Indem Paid] ,Claim ,Insured ,[Exp Paid]ORDER BY CSC.DispositionCode

I get the following error.
Msg 130, Level 15, State 1, Procedure PXQ7070A, Line 20
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Do I need to make this a function instead of a Proc?

0

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Sorry, left a mistake. Let's try this one instead:

USE [EXPORTpahpixPROD]GO/****** Object: StoredProcedure [dbo].[PXQ7070A] Script Date: 09/22/2011 07:53:59 ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGO/* ***************************************************************************************************//* Closed Claims Report *//* ***************************************************************************************************/ALTER PROCEDURE [dbo].[PXQ7070A]( @FromDate DATETIME , @ToDate DATETIME, @GblOptState VARCHAR(4))AS SET NOCOUNT ON DECLARE @GetMonth VARCHAR(2) DECLARE @GetYear VARCHAR(4) SET @GetMonth = MONTH(GETDATE()) SET @GetYear = YEAR(GETDATE())SELECT DISTINCT [Category] = CASE WHEN CSC.DispositionCode = '5' THEN 'Trial' WHEN CSC.DispositionCode = '6' THEN 'Trial' WHEN CSC.DispositionCode = ' ' AND AppealFlag = 'Y' THEN 'Trial' WHEN CSC.DispositionCode = '11' THEN 'Trial' WHEN CSC.DispositionCode = '12' THEN 'Trial' WHEN CSC.DispositionCode = '13' THEN 'Arbitration' WHEN CSC.DispositionCode = '14' THEN 'Arbitration' WHEN CSC.DispositionCode = '15' THEN 'Mediation' WHEN CSC.DispositionCode = '18' THEN 'Early Offer Claims' WHEN CSC.DispositionCode = '4' THEN 'Involuntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' THEN 'Voluntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' THEN 'Settlements' WHEN CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' THEN 'Incident Only Payment' END ,[Type] = CASE WHEN CSC.DispositionCode = '5' THEN 'Judgment for Plaintiff' WHEN CSC.DispositionCode = '6' THEN 'Judgment for Defendant' WHEN CSC.DispositionCode = ' ' AND AppealFlag = 'Y' THEN 'Pending Appeal' WHEN CSC.DispositionCode = '11' THEN 'For Plaintiff After Appeal' WHEN CSC.DispositionCode = '12' THEN 'For Defendant After Appeal' WHEN CSC.DispositionCode = '13' THEN 'Award for Plaintiff' WHEN CSC.DispositionCode = '14' THEN 'Award for Defense' WHEN CSC.DispositionCode = '15' THEN 'Mediation' WHEN CSC.DispositionCode = '18' THEN 'Early Offer Claims' WHEN CSC.DispositionCode = '4' THEN 'Involuntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' THEN 'Voluntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' THEN 'Settlements' WHEN CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' THEN 'Incident Only Payment' END ,LEFT(Claim, 9) AS Claim ,Insured ,CSC.DispositionCode ,[Indem Paid] ,[Exp Paid] ,SUM(CASE WHEN CSC.DispositionCode = '5' THEN CONVERT(money, [Indem Paid]) ELSE 0 END) AS SumJudgmentforPlaintiff ,SUM(CASE WHEN CSC.DispositionCode = '6' THEN CONVERT(money, [Indem Paid]) ELSE 0 END) AS SumJudgmentforDefendant ,SUM(CASE WHEN CSC.DispositionCode = '3' THEN CONVERT(money, [Indem Paid]) ELSE 0 END) AS SumInvoluntaryDismissalFROM EXPORTpahpixPROD.dbo.PXQ7000_UDF(@GetMonth, @GetYear, NULL, 0, 0, 0, 1, 0, '99-') INNER JOIN wellandp.ClaimSubclaim CSC ON dbo.PXQ7000_UDF.Claim = CSC.ClaimNoWHERE [Disposition Date] BETWEEN '01/01/2011' AND '09/01/2011' AND ( CSC.DispositionCode IN ( '4', '5', '6', '11', '12', '13', '14', '15', '18' ) OR ( CSC.DispositionCode = '3' AND [Indem Paid] >= '0.00' ) OR ( CSC.DispositionCode = 'E' AND AppealFlag = 'Y' ) ) AND (CurrentFlag = 'Y')GROUP BY CSC.DispositionCode ,AppealFlag ,[Indem Paid] ,Claim ,Insured ,[Exp Paid]ORDER BY CSC.DispositionCode

That executed without error. However I do not think I phrased my question correctly. I am using this data to feed a report so what I am expecting to see on the report side is.

please be more specific on what you are attempt to achieve...

give an example of the input and expected output

USE [EXPORTpahpixPROD]GO/****** Object: StoredProcedure [dbo].[PXQ7070A] Script Date: 09/22/2011 07:53:59 ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGO/* ***************************************************************************************************//* Closed Claims Report *//* ***************************************************************************************************/ALTER PROCEDURE [dbo].[PXQ7070A]( @FromDate DATETIME , @ToDate DATETIME, @GblOptState VARCHAR(4))AS SET NOCOUNT ON DECLARE @GetMonth VARCHAR(2) ,@GetYear VARCHAR(4) SElect @GetMonth = MONTH(GETDATE()) ,@GetYear = YEAR(GETDATE())Sekect [category],[type] ,sum(SumJudgmentforPlaintiff) AS SumJudgmentforPlaintiff,SUM(SumJudgmentforDefendant) AS SumJudgmentforDefendant,SUM(SumInvoluntaryDismissal) AS SumInvoluntaryDismissalFROM (SELECT [Category] = CASE WHEN CSC.DispositionCode = '5' THEN 'Trial' WHEN CSC.DispositionCode = '6' THEN 'Trial' WHEN CSC.DispositionCode = ' ' AND AppealFlag = 'Y' THEN 'Trial' WHEN CSC.DispositionCode = '11' THEN 'Trial' WHEN CSC.DispositionCode = '12' THEN 'Trial' WHEN CSC.DispositionCode = '13' THEN 'Arbitration' WHEN CSC.DispositionCode = '14' THEN 'Arbitration' WHEN CSC.DispositionCode = '15' THEN 'Mediation' WHEN CSC.DispositionCode = '18' THEN 'Early Offer Claims' WHEN CSC.DispositionCode = '4' THEN 'Involuntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' THEN 'Voluntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' THEN 'Settlements' WHEN CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' THEN 'Incident Only Payment' END ,[Type] = CASE WHEN CSC.DispositionCode = '5' THEN 'Judgment for Plaintiff' WHEN CSC.DispositionCode = '6' THEN 'Judgment for Defendant' WHEN CSC.DispositionCode = ' ' AND AppealFlag = 'Y' THEN 'Pending Appeal' WHEN CSC.DispositionCode = '11' THEN 'For Plaintiff After Appeal' WHEN CSC.DispositionCode = '12' THEN 'For Defendant After Appeal' WHEN CSC.DispositionCode = '13' THEN 'Award for Plaintiff' WHEN CSC.DispositionCode = '14' THEN 'Award for Defense' WHEN CSC.DispositionCode = '15' THEN 'Mediation' WHEN CSC.DispositionCode = '18' THEN 'Early Offer Claims' WHEN CSC.DispositionCode = '4' THEN 'Involuntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' --<-- IS THIS REALLY CHARACTER DATA? THEN 'Voluntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' THEN 'Settlements' WHEN CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' THEN 'Incident Only Payment' END ,LEFT(Claim, 9) AS Claim ,Insured ,SumJudgmentforPlaintiff = CONVERT(MONEY,CASE WHEN CSC.DispositionCode = '5' THEN [Indem Paid] ELSE 0.00 END) ,SumJudgmentforDefendant = CONVERT(MONEY,CASE WHEN CSC.DispositionCode = '6' THEN [Indem Paid] ELSE 0.00 END) ,SumInvoluntaryDismissal = CONVERT(MONEY,CASE WHEN CSC.DispositionCode = '3' THEN [Indem Paid] ELSE 0.00 END)FROM EXPORTpahpixPROD.dbo.PXQ7000_UDF(@GetMonth, @GetYear, NULL, 0, 0, 0, 1, 0, '99-') INNER JOIN wellandp.ClaimSubclaim CSC ON dbo.PXQ7000_UDF.Claim = CSC.ClaimNoWHERE [Disposition Date] BETWEEN @FROMDATE AND @TODATE AND ( CSC.DispositionCode IN ( '4', '5', '6', '11', '12', '13', '14', '15', '18' ) OR ( CSC.DispositionCode = '3' AND [Indem Paid] >= '0.00' ) OR ( CSC.DispositionCode = 'E' AND AppealFlag = 'Y' ) ) AND (CurrentFlag = 'Y')) AS XGROUP BY CATEFORY,TYPE ,Claim ,InsuredORDER BY 1,2,3,4RETURNGO

Open in new window