Selection for primary insurance names secondary same name in SQL

Posted on 2014-11-13
Last Modified: 2014-11-14
I am recreating a report from another program into our SQL database.  This report is looking for all patients based on facility that were discharged on a certain date  that had ESSENCE as their primary insurance and Admit and Primary Care phy are restricted.  It should also list their secondary and tertiary insurance as well.  

When I run this report in theSQL database program I get the correct patient name but lists the primary insurance (essence) twice InsuranceSeqOrderID of 1 for both BUT the Policy numbers are different???    
Please see below.

      RESULTS:: Due to patient confientiality, I will only show conflicting fields:

InsuranceID      InsuranceOrderID      PolicyNumber1      PolicyNumber2
ESSENCE              1                                      000077013       
ESSENCE              1                                      153813902       

InsuranceID                        InsuranceOrderID      PolicyNumber1      PolicyNumber2
ESSENCE                                 1                                      000077013       
ILLINOIS MEDICAID         2                                                                      153813902
Question by:kvrogers
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
  • 5
  • 2
  • 2
LVL 13

Accepted Solution

Koen Van Wielink earned 400 total points
ID: 40441818
I have a suspicion where this goes wrong, but it's almost impossible to figure out what the right query should be without the table definitions. Do you have some sample data for us (non-confidential please) to work with?

The issue must be with this join I think:

FROM livendb.dbo.AdmVisits AV (NOLOCK)-- Select * from livendb.dbo.AdmVisits where Status like 'ADM IN%'

      LEFT JOIN livendb.dbo.AdmInsuranceOrder  AIO (NOLOCK) --select * from livendb.dbo.AdmInsuranceOrder
            On AV.SourceID = AIO.SourceID
            and AV.VisitID = AIO.VisitID

Open in new window

From your result it shows that the table AV returns the same InsuranceID and InsuranceOrderID for both records. While AII might return different policy numbers, because AV always returns 1 as the insuranceOrderId your case statements don't function as expected.
But like I said, the correct answer is impossible to give without knowing the table structures and the remaining data of the conflicting records on which you are joining the tables. Can you give the above example with names/policy numbers, etc modified so you're not giving any confidential data away?
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40441824
Also, you mention both MySQL and MS SQL server in your topics. Which database do you use?
LVL 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 100 total points
ID: 40442103
Will be hard to return another InsuranceID since you are filtering only by 'ESSENCE':
WHERE (...)
AND (AIO.InsuranceID LIKE 'ESSENCE' AND AIO.InsuranceOrderID = '1')

Open in new window

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!


Author Comment

ID: 40442597
Koen.  I am using Microsoft SQL Server 2008 R2.   I will look at the tables and send more info later.

Author Comment

ID: 40442697
I am attaching a spreadsheet of all the fields used in both reports on separate spreadsheets.
I have changed all the pertinent information regarding the patient.


Author Closing Comment

ID: 40442924
Thank you both for your help.

Author Comment

ID: 40442939
The solution was both the join and the where clause for the insurance.  I also found I could eliminate the CASE WHEN clauses in my selects by simply using  AIO.InsuranceID and  AII.PolicyNumber,

Here is the solution that worked:

FROM livendb.dbo.AdmVisits AV (NOLOCK)-- Select * from livendb.dbo.AdmVisits

      LEFT JOIN livendb.dbo.AdmittingData AD (NOLOCK)-- select * from livendb.dbo.AdmittingData
                        On AV.SourceID = AD.SourceID
                        and AV.VisitID = AD.VisitID
        LEFT JOIN livendb.dbo.AdmDischarge ADC (NOLOCK) -- Select * from livendb.dbo.AdmDischarge
                        ON AV.SourceID = ADC.SourceID
                        AND AV.VisitID = ADC.VisitID
      LEFT JOIN livendb.dbo.AdmInsuranceOrder  AIO (NOLOCK) --select * from livendb.dbo.AdmInsuranceOrder
            On AV.SourceID = AIO.SourceID
            and AV.VisitID = AIO.VisitID
            LEFT JOIN livendb.dbo.AdmInsuredInfo AII (NOLOCK)-- select * from livendb.dbo.AdmInsuredInfo
                        on AIO.SourceID = AII.SourceID
                        AND AIO.VisitID = AII.VisitID
                        AND AIO.InsuranceID = AII.InsuranceID
       LEFT JOIN livendb.dbo.AdmProviders AP (NOLOCK) --select * from livendb.dbo.AdmProviders
                  on AV.SourceID = AP.SourceID
                  AND AV.VisitID = AP.VisitID
                        LEFT JOIN livendb.dbo.DMisUsers  DMU (NOLOCK) --SELECT * FROM livendb.dbo.DMisUsers
                                    ON AP.SourceID = DMU.SourceID
                                    AND AP.AdmitID = DMU.UserID
                        LEFT JOIN livendb.dbo.DMisUsers  DMU_2(NOLOCK) --SELECT * FROM livendb.dbo.DMisUsers
                                    ON AP.SourceID = DMU_2.SourceID
                                    AND AP.PrimaryCareID = DMU_2.UserID

WHERE Convert(Varchar,ADC.DischargeDateTime,101) BETWEEN @BeginDate AND @EndDate
AND AV.FacilityID = 'BMH'
AND AV.PrimaryInsuranceID = 'ESSENCE'
AND AV.Status = 'DIS IN'
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40442955
You still have the filter set to 'ESSENCE':

WHERE Convert(Varchar,ADC.DischargeDateTime,101) BETWEEN @BeginDate AND @EndDate
 AND AV.FacilityID = 'BMH'
 AND AV.PrimaryInsuranceID = 'ESSENCE'
 AND AV.Status = 'DIS IN'

Author Comment

ID: 40443424
Yes, but in my select I am using this:

AIO.InsuranceID  to pull any insurance that the patient may have

but in the WHERE CLAUSE  I am using
AV.Primary Insurance

I did this so that it would show in my report both the primary and secondary insurance but limited it that the primary insurance be only ESSENCE.

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP: Filling Out/Creating a PDF 29 94
Need help with a query 14 36
php hashing methods 3 10
Find special characters using tSQL 6 15
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Introduction This article is intended for those who are new to PHP error handling (  It addresses one of the most common problems that plague beginning PHP develop…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

763 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