Solved

Selection for primary insurance names secondary same name in SQL

Posted on 2014-11-13
9
242 Views
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       

SHOULD SAY:
InsuranceID                        InsuranceOrderID      PolicyNumber1      PolicyNumber2
ESSENCE                                 1                                      000077013       
ILLINOIS MEDICAID         2                                                                      153813902
Patients-Discharged-with-Essence.docx
0
Comment
Question by:kvrogers
  • 5
  • 2
  • 2
9 Comments
 
LVL 12

Accepted Solution

by:
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?
0
 
LVL 12

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?
0
 
LVL 45

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

0
 

Author Comment

by:kvrogers
ID: 40442597
Koen.  I am using Microsoft SQL Server 2008 R2.   I will look at the tables and send more info later.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:kvrogers
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.

Thanks,
Problem-Results.xlsx
0
 

Author Closing Comment

by:kvrogers
ID: 40442924
Thank you both for your help.
0
 

Author Comment

by:kvrogers
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'
0
 
LVL 45

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'
0
 

Author Comment

by:kvrogers
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.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now