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