Solved

Stored Procedure (Sum)

Posted on 2011-09-22
7
221 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 500 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

690 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