?
Solved

SQL 2005 Stored Procedure Count

Posted on 2011-09-22
1
Medium Priority
?
178 Views
Last Modified: 2012-08-13
In the attached Stored Procedure I am getting the Counts/Sum's for Insureds, Cases, Indemnity Paid and Expense Paid by Type. I also have a need to get the counts for these fields by Category. So for example if I have the following.

Category = Arbitration      
Type                      Indemnity Paid      Expense Paid      Cases      Insureds
Award for Defense      100000.00      217766.79      3      3
Award for Plaintiff       830000.00      311405.90      3      3


                              930000.00              529172.69               6              6 I need these totals for each category.
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  [Category]
		   ,[Type]
           ,SUM([Indem Paid]) AS [Indemnity Paid]
           ,SUM([Exp Paid]) AS [Expense Paid]
           ,COUNT( DISTINCT Claim) AS [Cases]
           ,COUNT(Insured) AS Insureds
    FROM    ( SELECT DISTINCT
						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 AS [Category],
                        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 [Category], [Type]
    ORDER BY [Category], [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
1 Comment
 
LVL 16

Accepted Solution

by:
vdr1620 earned 2000 total points
ID: 36582635
Try using "WITH ROLLUP" in the group by statement

Ref: http://msdn.microsoft.com/en-us/library/ms189305(v=sql.90).aspx
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 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