Solved

SQL 2005 Stored Procedure COUNT

Posted on 2011-09-22
2
211 Views
Last Modified: 2012-06-21
I have the attached query that produces a result set similar to the example below. The issue I am having is with the Cases Count. Cases come from the Claim Field which look like this (1-06-0043B, 1-06-0043E, 1-05-0022A, 1-06-0029) What I am trying to accomplish is to Drop the tenth Character like so (1-06-0043, 1-06-0043, 1-05-0022, 1-06-0029) and when a claim number is the same only count it once. So for the example below the Cases Count for Involuntary Dismissal would be 3 since (1-06-0043) displays twice and the insured Count would remain the same. The reason for this is because I have 4 records for Involuntary Dismissal but two of them are attached to the same claim.

Type                                      Indemnity Paid      Expense Paid      Cases      Insureds
Involuntary Dismissal      0.00                      131306.23      4      4
Judgment for Defendant      0.00                      390134.69      5      5

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 [Indemnity Paid]
           ,SUM([Exp Paid]) AS [Expense Paid]
           ,COUNT(Claim) AS [Cases]
           ,COUNT(Insured) AS Insureds
    FROM    ( SELECT DISTINCT
                        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]
                       ,[Exp Paid]
                       ,LEFT(Claim, 9) AS Claim
                       ,Insured
              FROM      EXPORTpahpixPROD.dbo.PXQ7000_UDF(@GetMonth, @GetYear,
                                                         NULL, 0, 0, 0, 1, 0,
                                                         @GblOptState)
                        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' )
            ) tmp
    GROUP BY [Type]
    ORDER BY [Type]

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
2 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 36582093
Try this:

,COUNT( DISTINCT Claim) AS [Cases]
0
 

Author Closing Comment

by:mburk1968
ID: 36582118
That's it! Thank You!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

737 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