• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

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

0
mburk1968
Asked:
mburk1968
  • 3
  • 3
1 Solution
 
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
 
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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
 
zvytasCommented:
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now