Stored Procedure (Sum)

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 OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/* ***************************************************************************************************/
/*  		                       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
--			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.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')
GROUP BY CSC.DispositionCode
		,AppealFlag
		,[Indem Paid]
		,Claim
		,Insured
		,[Exp Paid]
ORDER BY CSC.DispositionCode

Open in new window

mburk1968Asked:
Who is Participating?
 
zvytasConnect With a Mentor Commented:
Here you go:

USE [EXPORTpahpixPROD]
GO
/****** Object:  StoredProcedure [dbo].[PXQ7070A]    Script Date: 09/22/2011 07:53:59 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/* ***************************************************************************************************/
/*  		                       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')
	) tmp
GROUP BY [Type]
ORDER BY [Type]

Open in new window

0
 
zvytasCommented:
Try the following:

USE [EXPORTpahpixPROD]
GO
/****** Object:  StoredProcedure [dbo].[PXQ7070A]    Script Date: 09/22/2011 07:53:59 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/* ***************************************************************************************************/
/*  		                       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 SumInvoluntaryDismissal
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')
GROUP BY CSC.DispositionCode
		,AppealFlag
		,[Indem Paid]
		,Claim
		,Insured
		,[Exp Paid]
ORDER BY CSC.DispositionCode

Open in new window

0
 
mburk1968Author Commented:
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
Cloud Class® Course: Certified Penetration Testing

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.

 
zvytasCommented:
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 OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/* ***************************************************************************************************/
/*  		                       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 SumInvoluntaryDismissal
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')
GROUP BY CSC.DispositionCode
		,AppealFlag
		,[Indem Paid]
		,Claim
		,Insured
		,[Exp Paid]
ORDER BY CSC.DispositionCode

Open in new window

0
 
mburk1968Author Commented:
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.

Involuntary Dismissals = $20,000

Instead what I see is

Involuntary Dismissals = $5,000
Involuntary Dismissals = $10,000
Involuntary Dismissals = $2,500
Involuntary Dismissals = $2,500

 Sorry about the confusion.
0
 
mburk1968Author Commented:
Thank You so much!
0
 
LowfatspreadCommented:
MORE like this?

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 OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/* ***************************************************************************************************/
/*  		                       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 SumInvoluntaryDismissal
FROM (

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.ClaimNo
WHERE   [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 X
GROUP BY CATEFORY,TYPE		
		,Claim
		,Insured
		
ORDER BY 1,2,3,4

RETURN

GO

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.