Results 1 to 7 of 7

Thread: Grouping Help

  1. #1
    Join Date
    Jan 2012
    Posts
    24

    Unanswered: Grouping Help

    I'm having a problem with getting my SQL correct, so that it combines like names into a single row. Below is what the SQL currently returns.

    HTML Code:
    Pharma                        Percent
    Atley                         1.0
    Atley Pharmaceuticals         1.0
    Amgen                         4.0
    Cephalon                      1.0
    Shire                         3.0
    Shire Pharmaceuticals         3.0
    UCB                           1.0
    UCB Pharmaceuticals           1.0
    Merck                         5.0
    What I need is the resultset to be returned like below.

    HTML Code:
    Pharma                        Percent
    Atley                         2.0
    Amgen                         4.0
    Cephalon                      1.0
    Shire                         6.0
    Merck                         5.0
    Here is my current SQL I have a date hard coded date only for testing purposes.

    Code:
    DECLARE @StartDate datetime, @EndDate datetime
    SET @StartDate = '1/1/2012'
    SET @EndDate = '3/1/2012'
    
    BEGIN
    	SET NOCOUNT ON;
        
    	SELECT 
    		pb.Pharma,
    		CONVERT(DECIMAL(3,1),
    		(COUNT(pb.Claims))) AS [Paid Percent]
    	FROM
    		(
    			SELECT 
    				DISTINCT ps.Pharma AS Pharma,
    				(COUNT(p.UNIQUE_ID) * 100 / (SELECT 
    							      COUNT(Unique_ID) 
    							     FROM 
    							      Claims
    							     WHERE 
    							      [TimeStamp] BETWEEN @StartDate + '00:00:00.000' AND @EndDate + '23:59:59.999')) AS Claims
    			FROM 
    				Claims
    				INNER JOIN Plans p
    				ON a.Plan_ID = p.Unique_ID
    				INNER JOIN ProgSpecs ps
    				ON ps.GroupNumber = p.Group_ID
    			WHERE 
    				ps.ExpirationDate < GETDATE() AND 
    				a.[TimeStamp] BETWEEN @StartDate + '00:00:00.000' AND @EndDate + '23:59:59.999'
    			GROUP BY 
    				ps.Pharma, 
    				ps.GroupNumber
    		) pb
    
    GROUP BY 
    	pb.Pharma
    END
    Can someone please help me to get the desired results?

  2. #2
    Join Date
    Nov 2004
    Posts
    54
    I would change:

    Code:
    DISTINCT ps.Pharma AS Pharma,
    to be

    Code:
    DISTINCT substring(ps.Pharma,0,(findstring(ps.Pharma,' ')-1) AS Pharma,
    That should group together all of the larger groups. I'm sure there is a better way of doing it.

  3. #3
    Join Date
    Jan 2012
    Posts
    24
    Thanks for the reply. Unfortunately I don't have a function available by the name of "findstring".

  4. #4
    Join Date
    Nov 2004
    Posts
    54
    Sorry about that. Try using charindex() instead.

  5. #5
    Join Date
    Jan 2012
    Posts
    24
    I had tried that previously and while it removed for instance Atley Pharmaceuticals instead of getting:

    HTML Code:
    Pharma                        Paid Percent
    Atley                         2.0
    I got

    HTML Code:
    Pharma                        Paid Percent
    Atley                         1.0
    Any ideas on how I can get it to add the 2 values and return a single row?

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    -- translate all the similar pharma names to one name per pharma group. 
    -- Should ideally be done with pharma Id's or groupnumber, ... 
    CREATE TABLE PharmaNameTranslation( 
    	SourceName	VARCHAR(100)	NOT NULL,
    	DestName	VARCHAR(100)	NOT NULL,
    	CONSTRAINT pk_PharmaNameTranslation PRIMARY KEY (SourceName)
    )
    
    INSERT INTO PharmaNameTranslation(SourceName, DestName) VALUES
    ('Atley', 'Atley'), 
    ('Atley Pharmaceuticals', 'Atley'), 
    ('Amgen', 'Amgen'),
    ('Cephalon', 'Cephalon'), 
    ('Shire', 'Shire'), 
    ('Shire Pharmaceuticals', 'Shire'), 
    ('UCB', 'UCB'), 
    ('UCB Pharmaceuticals', 'UCB'), 
    ('Merck', 'Merck')
    
    DECLARE @StartDate datetime, @EndDate datetime
    SET @StartDate = '1/1/2012'
    SET @EndDate = '4/1/2012'
    
    DECLARE @TotalClaimsInPeriod INT
    
    SELECT @TotalClaimsInPeriod = COUNT(Unique_ID) 
    FROM Claims
    WHERE [TimeStamp] >= @StartDate AND 
    	[TimeStamp] < @EndDate)) AS Claims
    
    SELECT 
    	PharmaNameTranslation.DestName AS Pharma,
    	(COUNT(p.UNIQUE_ID) * 100.0 / @TotalClaimsInPeriod) AS Claims
    FROM 
    	Claims a
    		INNER JOIN Plans p
    			ON a.Plan_ID = p.Unique_ID
    		INNER JOIN ProgSpecs ps
    			ON ps.GroupNumber = p.Group_ID
    		INNER JOIN PharmaNameTranslation
    			ON ps.Pharma = PharmaNameTranslation.SourceName
    WHERE 
    	ps.ExpirationDate < GETDATE() AND 
    	a.[TimeStamp] >= @StartDate AND
    	a.[TimeStamp] < @EndDate
    GROUP BY 
    	PharmaNameTranslation.DestName
    I'm not sure if everything is OK, but it should get you started.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Uglier than the south end of a north-bound mule, but:
    Code:
    CREATE TABLE #foo (
       Pharma       NVARCHAR(25)	NOT NULL
    ,  [Percent]    FLOAT           NOT NULL
    )
    
    INSERT INTO #foo (Pharma, [Percent])
       VALUES ('Atley',                         1.0)
    ,     (    'Atley Pharmaceuticals',         1.0)
    ,     (    'Amgen',                         4.0)
    ,     (    'Cephalon',                      1.0)
    ,     (    'Shire',                         3.0)
    ,     (    'Shire Pharmaceuticals',         3.0)
    ,     (    'UCB',                           1.0)
    ,     (    'UCB Pharmaceuticals',           1.0)
    ,     (    'Merck',                         5.0)
    
    SELECT
       CASE
          WHEN 0 = PATINDEX('%[^A-Za-z0-9]%', a.Pharma) THEN a.Pharma
          ELSE LEFT(a.Pharma, PATINDEX('%[^A-Za-z0-9]%', a.Pharma) - 1)
       END AS first_word
    ,  SUM(a.[Percent]) AS 'Percent'
       FROM #foo AS a
       GROUP BY CASE
          WHEN 0 = PATINDEX('%[^A-Za-z0-9]%', a.Pharma) THEN a.Pharma
          ELSE LEFT(a.Pharma, PATINDEX('%[^A-Za-z0-9]%', a.Pharma) - 1)
       END
       ORDER BY 1
       
    DROP TABLE #foo
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •