Selection for primary insurance names secondary same name in SQL

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Also, you mention both MySQL and MS SQL server in your topics. Which database do you use?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

kvrogersAuthor Commented:
Koen.  I am using Microsoft SQL Server 2008 R2.   I will look at the tables and send more info later.
kvrogersAuthor Commented:
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.

kvrogersAuthor Commented:
Thank you both for your help.
kvrogersAuthor Commented:
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'
Vitor MontalvãoMSSQL Senior EngineerCommented:
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'
kvrogersAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.