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
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
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
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
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
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]
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
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.