Link to home
Start Free TrialLog in
Avatar of 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       

InsuranceID                        InsuranceOrderID      PolicyNumber1      PolicyNumber2
ESSENCE                                 1                                      000077013       
ILLINOIS MEDICAID         2                                                                      153813902
Avatar of Koen Van Wielink
Koen Van Wielink
Flag of Netherlands image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Also, you mention both MySQL and MS SQL server in your topics. Which database do you use?
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kvrogers


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

Thank you both for your help.
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'
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'
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.