Avatar of kvrogers
kvrogers
 asked on

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       

SHOULD SAY:
InsuranceID                        InsuranceOrderID      PolicyNumber1      PolicyNumber2
ESSENCE                                 1                                      000077013       
ILLINOIS MEDICAID         2                                                                      153813902
Patients-Discharged-with-Essence.docx
MySQL ServerMicrosoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
kvrogers

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Koen Van Wielink

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Koen Van Wielink

Also, you mention both MySQL and MS SQL server in your topics. Which database do you use?
SOLUTION
Vitor Montalvão

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
kvrogers

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

ASKER
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.

Thanks,
Problem-Results.xlsx
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
kvrogers

ASKER
Thank you both for your help.
kvrogers

ASKER
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ão

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'
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
kvrogers

ASKER
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.