none
Group By Question

    Question

  • I work at a Medical Group which deals with 30 different health plans.  Our issue is that Membership data comes from the health plans and populates our Database If an Employer offers multiple health plan option our Employer Table is duplicated up the wazoo.  I'm trying to work out a solution.

    Sample Data

    Emp Group                    Emp Name          

    7                                   Disney Worldwide Services

    7                                  Disney Worldwide Services Inc

    7                                 Disney Worldwide Services, Inc

    So any report that looks at this employer gets 3 records for each member as the link is by Emp Group.  In MS Access I can use the Group by on EmpGroup and First on Emp Name to reduce it to 1 Record.  However we're moving away from Access and more to Reporting Services Is there a way to do something similar in SQL Server?

    Tuesday, July 02, 2013 7:54 PM

Answers

  • You can still do

    SELECT EmpGroup, MAX(EmpName)
    FROM ..
    GROUP BY EmpGroup

    But depending on what you start joining in, this may not be what you want. 

    I would try to work out a solution for consolidating the data so you have only one Emp Name for each unique Emp.



    Bob - www.crowcoder.com

    Tuesday, July 02, 2013 8:13 PM

All replies

  • You can still do

    SELECT EmpGroup, MAX(EmpName)
    FROM ..
    GROUP BY EmpGroup

    But depending on what you start joining in, this may not be what you want. 

    I would try to work out a solution for consolidating the data so you have only one Emp Name for each unique Emp.



    Bob - www.crowcoder.com

    Tuesday, July 02, 2013 8:13 PM
  • Perhaps the issue is your schema - seems your model assumes that "plan" and "employer" are one and the same?
    Tuesday, July 02, 2013 8:28 PM
  • No the Schema is ok The issue is That the Data comes from multiple Health Plans  Aetna, Secure Horizons, PacifiCare for example.  The Health plans send us their membership data including names, address, employers ...  The problem is that an Employer may have multiple healthplan options for their Employee. 

    Say  ABC Company allows the employee to select from Aetna, US Healthcare or IEHP.  The employee then selects his provider.  So We may have 500 Employees from ABC Company with 100 from Aetna 100 from IEHP and 300 from US Healthcare.    But Aetna may have the Employer as ABC Co.  US Healthcare May have them as ABC Company and IEHP May have it as ABC  So 3 records get added.  Now we want to run a query that shows how many employees we have by Employer  Well Since all 3 have the same Group Number we now have 1500 employees for ABC Company  Since each of those 500 members have the Group Number for ABC Company and the Employer table has 3 Company names for that Group Number.

    Tuesday, July 02, 2013 9:57 PM
  • >> But Aetna may have the Employer as ABC Co.  US Healthcare May have them as ABC Company and IEHP May have it as ABC  So 3 records [sic: rows are not records] get added.  << 

    Have you talked to a scrubbing vendor? I would use a DUNS for the companies, since that is the universal identifier for business entities. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, July 03, 2013 2:27 AM
  • I would argue your schema is not ok. I would try to inject a step into the import that figures out what company they actually mean and when it is found (or inserted if not exist yet), then you use its ID to relate the employees. You can use a Levenstein algorithm to help figure out the company record but it may require a human to actually look at the mappings it will suggest.

    That said, of course I can't know all the details of your system.

    Lastly, anything you are doing in Access can be done in .Net/SQL. It may not translate 1 to 1, but at least you can recreate whatever you are doing now.


    Bob - www.crowcoder.com

    Wednesday, July 03, 2013 12:09 PM
  • I agree however Access is the platform of choice here has been since before I came here.  I have no control or say in the import portion that is done at corporate headquarters.  I simply have to deal with what they do.  However I did come up with a solution.  I have an intermediate query in Access that groups by the Employer Group Number and takes the first Employer Name.  Not the most elegant but it works.  I no longer get the duplication that was giving me headaches and the totals calculate correctly now.
    Wednesday, July 03, 2013 3:34 PM
  • Hi Ray1127,

    After reading your question, it seems that you are trying to take the following three emp name as the same one. If there is any misunderstanding, please let me know.

    'Disney Worldwide Services'
    'Disney Worldwide Services Inc'
    'Disney Worldwide Services, Inc'

    We can use the following codes to move “Inc” and “, Inc” from the emp name, and then we can group the record according to emp group and emp name.

    declare @MedicalGroup table
    (
    	[EmpGroup] int
    	,[EmpName] varchar(100)
    )
    
    insert into @MedicalGroup values (7,'Disney Worldwide Services')
    ,(7,'Disney Worldwide Services Inc')
    ,(7,'Disney Worldwide Services, Inc')
    
    ;with cte as
    (
    select case CHARINDEX('cnI ,',reverse([EmpName]))
    when 1 then
    	REVERSE(
    		SUBSTRING(reverse([EmpName]),len('cnI ,')+1,len([EmpName])-len('cnI ,'))
    	)
    else
    	case CHARINDEX('cnI',reverse([EmpName]))
    	when 1 then
    	REVERSE(
    		SUBSTRING(reverse([EmpName]),len('cnI ')+1,len([EmpName])-len('cnI '))
    	)
    	else 
    	[EmpName]
    	end
    end as [EmpName],[EmpGroup]
    from @MedicalGroup
    )
    
    select [EmpGroup],[EmpName]
    from cte
    group by [EmpGroup],[EmpName]
    


    Allen Li
    TechNet Community Support

    Thursday, July 04, 2013 2:13 AM
  • Unfortunately I am not allowed to modify the database in any way.  I have read only access to generate reports and that's it.  I considered doing that in a query but the way I'm doing it is satisfactory and faster.  At least my boss likes it since I'm getting correct membership numbers by Employer Group which is something they've been wanting and unable to get.  I have forwarded the suggestion to the DBA we'll see if anything gets done.  I doubt it though since membership is updated monthly by each healthplan and this would happen every month.

    Friday, July 05, 2013 4:32 PM