Solved

SQL Query Sum

Posted on 2011-09-21
4
254 Views
Last Modified: 2012-05-12
I have a need to total the claim field by CSC.DispositionCode. My claim numbers look like so
1-06-0139A
1-06-0139B
1-06-0217
1-06-0156

However if 1-06-0139A, 1-06-0139B have the same CSC.Disposition code they only count as 1 Case so I need to strip off the the tenth character and then sum as Cases.

USE [EXPORTpahpixPROD]
GO
/****** Object:  StoredProcedure [dbo].[PXQ7070]    Script Date: 09/21/2011 07:27:31 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/* ***************************************************************************************************/
/*  		                       Closed Claims Report    	                                     */
/* ***************************************************************************************************/
 
ALTER PROCEDURE [dbo].[PXQ7070]
(     
      @AsOfMonth DATETIME ,
      @AsOfYear DATETIME ,
	  @FromDate DATETIME ,
      @ToDate DATETIME,
      @GblOptState VARCHAR(4)

)
AS 
    SET NOCOUNT ON

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
       ,Claim
       ,Claimant
       ,Insured
       ,Risk
       ,[Loss Date]
       ,CSC.NoticeDate
       ,[Company Position]
       ,[Disposition Date]
       ,CSC.DispositionCode
       ,[Plaintiff Counsel]
       ,[Defense Counsel]k
       ,CSC.Venue
       ,[Indem Paid]
       ,[Exp Paid]
       ,Examiner
FROM    EXPORTpahpixPROD.dbo.PXQ7000_UDF(@AsOfMonth, @AsOfYear, 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')
ORDER BY CSC.DispositionCode

Open in new window

0
Comment
Question by:mburk1968
  • 3
4 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 36577841
If the values are always 10 characters, then you can use LEFT(Claim, 9) pending Claim is the correct column name.
0
 

Author Comment

by:mburk1968
ID: 36579837
Once I have stripped off the 10th character how do I sum the claims by disposition code? Also I only want to count claims that are identical as 1 if they have the same disposition code.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36580751
You would use a GROUP BY and include the disposition code then COUNT(DISTINCT { the column stripped of 10th character }) ... If you need to maintain detail, then you should look at COUNT() OVER() wi PARTITION BY being your disposition code and count distinctly the left 9 characters.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36582896
I am glad that helped. Sorry I was short on details as I was in training and on my ipad when posting. If you need any further details, please let me know; however, it looks like you had some follow-up questions already answered by others.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now