Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Stored Procedure (Sum)

Posted on 2011-09-22
7
Medium Priority
?
224 Views
Last Modified: 2012-05-12
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
Comment
Question by:mburk1968
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 5

Expert Comment

by:zvytas
ID: 36580606
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
 

Author Comment

by:mburk1968
ID: 36580625
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
 
LVL 5

Expert Comment

by:zvytas
ID: 36580640
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

 

Author Comment

by:mburk1968
ID: 36580701
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
 
LVL 5

Accepted Solution

by:
zvytas earned 2000 total points
ID: 36580784
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
 

Author Closing Comment

by:mburk1968
ID: 36580823
Thank You so much!
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36580851
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question