Another Join?

clangel · October 6, 2015, 1:15pm

So I think I need another join in this query. My results need to be cancellation/no show appointments (1st and 2nd only) for the past five years for the doctors identified below. Right now my query results show me the same appointment date for all doctors for the same patient profile ID. That is not possible.

DECLARE 
 @StartDate Datetime = '2010-10-01'
 , @EndDate DateTime = '2015-10-01'

SELECT     Appointments.ApptStart, Appointments.Status, DoctorFacility.Last, DoctorFacility.First, PatientVisit.PatientVisitId, 
            PatientVisit.PatientProfileId
FROM         DoctorFacility, PatientVisit
                      
                      
           left join Appointments ON
                      Appointments.PatientVisitID=PatientVisit.PatientvisitID
                      
                      
 where    ( (DoctorFacility.Last = 'Cambell')  OR
                      (DoctorFacility.Last = 'Frost') OR
                      (DoctorFacility.Last = 'Maden') OR
                      (DoctorFacility.Last = 'Morell') OR
                      (DoctorFacility.Last = 'Schott'))
                      AND (Appointments.Status like 'Cancel%' or Appointments.Status like 'No show%')

Do I need another join? If so, what should I join? This may be a more complicated scenario but I have to start somewhere.

khtan · October 6, 2015, 1:19pm

How do you join those 2 tables ?

clangel · October 6, 2015, 1:27pm

The joins for these two tables are:

DoctorFacility INNER JOIN
PatientVisit ON DoctorFacility.DoctorFacilityId = PatientVisit.OtherDoctorId AND DoctorFacility.DoctorFacilityId = PatientVisit.OperatingDoctorId AND
DoctorFacility.DoctorFacilityId = PatientVisit.AdmittingDoctorId AND DoctorFacility.DoctorFacilityId = PatientVisit.AttendingDoctorId AND
DoctorFacility.DoctorFacilityId = PatientVisit.PCPId AND DoctorFacility.DoctorFacilityId = PatientVisit.CompanyId AND
DoctorFacility.DoctorFacilityId = PatientVisit.DoctorId AND DoctorFacility.DoctorFacilityId = PatientVisit.FacilityId AND
DoctorFacility.DoctorFacilityId = PatientVisit.SupervisingDoctorId AND DoctorFacility.DoctorFacilityId = PatientVisit.ReferringDoctorId

These are joins that have been previously coded. I do not think I need them all.

khtan · October 6, 2015, 1:30pm

i don't see those in your query at all.

Is that the full query that you have posted ?

clangel · October 6, 2015, 1:35pm

That join is not in my original query.

DECLARE 
 @StartDate Datetime = '2010-10-01'
 , @EndDate DateTime = '2015-10-01'

SELECT     Appointments.ApptStart, Appointments.Status, DoctorFacility.Last, DoctorFacility.First, PatientVisit.PatientVisitId, 
            PatientVisit.PatientProfileId
FROM         DoctorFacility, PatientVisit
                      
                      
           left join Appointments ON
                      Appointments.PatientVisitID=PatientVisit.PatientvisitID
                      
                      
 where    ( (DoctorFacility.Last = 'Cambell')  OR
                      (DoctorFacility.Last = 'Frost') OR
                      (DoctorFacility.Last = 'Maden') OR
                      (DoctorFacility.Last = 'Morell') OR
                      (DoctorFacility.Last = 'Schott'))
                      AND (Appointments.Status like 'Cancel%' or Appointments.Status like 'No show%')

I do think a join needs to added. That is my question. Do I need to add an additional join and if so, where, what?

khtan · October 6, 2015, 1:40pm

Frankly speaking, I have no idea. You have not provided us with sufficient information on your requirement, description on the tables, sample data and expected result

But looking at your query, you have a cartesian join on DoctorFacility and PatientVisit. Usually this means you will get unwanted or duplicate in your result .

clangel · October 6, 2015, 1:58pm

I am sorry I haven't provided enough information. I am very new at creating queries based on multiple tables. I will try to do a better job of explaining.

What I have set up so far is:

DECLARE 
 @StartDate Datetime = '2010-10-01'
 , @EndDate DateTime = '2015-10-01'

SELECT     Appointments.ApptStart, Appointments.Status, DoctorFacility.Last, DoctorFacility.First, PatientVisit.PatientVisitId, 
            PatientVisit.PatientProfileId
FROM         DoctorFacility, PatientVisit
                      
                      
           left join Appointments ON
                      Appointments.PatientVisitID=PatientVisit.PatientvisitID
                      
                      
 where    ( (DoctorFacility.Last = 'Cambell')  OR
                      (DoctorFacility.Last = 'Frost') OR
                      (DoctorFacility.Last = 'Maden') OR
                      (DoctorFacility.Last = 'Morell') OR
                      (DoctorFacility.Last = 'Schott'))
                      AND (Appointments.Status like 'Cancel%' or Appointments.Status like 'No show%')

The results I need:
Rates of no show/canceled for 1st & 2nd appointments for providers included in the query. The data I need resides in multiple tables (i.e., Doctor Facility, Patient profile, & appointments.) Some of these tables contain multiple relationships (i.e., the sample I provided answering your last question).

What do you mean by description of the tables? What information is necessary for me to supply that would help me to ask my questions better?

My expected results are how many cancel/no show appointments does each provider have for 1st and 2nd appointments over the past five years. My calculations can be done in Excel if I can get the correct raw data from SQL. So far, my results look like this:

ApptStart Status Last PatientVisitId PatientProfileId
3/31/2011 No show Maden xxx xxx
3/31/2011 No show Schott xxx xxx
3/31/2011 No show Schott xxx xxx
3/31/2011 No show Maden xxx xxx
3/31/2011 No show Frost xxx xxx
3/31/2011 No show Cambell xxx xxx
3/31/2011 No show Morell xxx xxx
3/31/2011 No show Morell xxx xxx
6/17/2011 Cancel/Doctor Maden xxx xxx
6/17/2011 Cancel/Doctor Schott xxx xxx
6/17/2011 Cancel/Doctor Schott xxx xxx
6/17/2011 Cancel/Doctor Maden xxx xxx
6/17/2011 Cancel/Doctor Frost xxx xxx
6/17/2011 Cancel/Doctor Cambell xxx xxx
6/17/2011 Cancel/Doctor Morell xxx xxx
6/17/2011 Cancel/Doctor Morell xxx xxx

For the purposes of this post I have changed some of the data.

Please be patient with me. I feel like progress is being made.

khtan · October 6, 2015, 2:10pm

You are definitely missing the JOIN on those two table.

Perhaps the join condition should be OR rather than AND ?

Try

DECLARE @StartDate     Datetime = '2010-10-01',
        @EndDate       DateTime = '2015-10-01'

SELECT  Appointments.ApptStart, 
        Appointments.Status,     
        DoctorFacility.Last, 
        DoctorFacility.First, 
        PatientVisit.PatientVisitId, 
        PatientVisit.PatientProfileId
FROM          DoctorFacility
INNER JOIN    PatientVisit    
        ON    DoctorFacility.DoctorFacilityId = PatientVisit.OtherDoctorId 
        OR    DoctorFacility.DoctorFacilityId = PatientVisit.OperatingDoctorId 
        OR    DoctorFacility.DoctorFacilityId = PatientVisit.AdmittingDoctorId 
        OR    DoctorFacility.DoctorFacilityId = PatientVisit.AttendingDoctorId 
        OR    DoctorFacility.DoctorFacilityId = PatientVisit.PCPId 
        OR    DoctorFacility.DoctorFacilityId = PatientVisit.CompanyId 
        OR    DoctorFacility.DoctorFacilityId = PatientVisit.DoctorId 
        OR    DoctorFacility.DoctorFacilityId = PatientVisit.FacilityId 
        OR    DoctorFacility.DoctorFacilityId = PatientVisit.SupervisingDoctorId 
        OR    DoctorFacility.DoctorFacilityId = PatientVisit.ReferringDoctorId
LEFT JOIN    Appointments     
        ON    Appointments.PatientVisitID    = PatientVisit.PatientvisitID
WHERE    ( 
          (DoctorFacility.Last = 'Cambell')  
    OR    (DoctorFacility.Last = 'Frost') 
    OR    (DoctorFacility.Last = 'Maden') 
    OR    (DoctorFacility.Last = 'Morell') 
    OR    (DoctorFacility.Last = 'Schott')
    )
        AND 
    (
        Appointments.Status like 'Cancel%' 
    OR    Appointments.Status like 'No show%'
    )

clangel · October 6, 2015, 2:21pm

I appreciate the help but that gave me exactly one result.

Is the reason I am having trouble with this because there are so many places that doctor facility contains joins with patient visit? I really only care about the join DoctorFacility.DoctorFacilityID=PatientVisit.FacilityID. Other doctor, operating doctor, etc is not information I need or care about. I thought If I write the queries instead of using query editor I would get better results since I can set my own joins. Is that correct?

khtan · October 7, 2015, 12:38pm

What query editor is that ? I doubt there aren't many of us uses that. We usually code the query directly in the Query Window of SQL Sever Management Studio.

For others to help you, you need to provide sufficient information

Show the schema of the related tables. Best is if you can post the CREATE TABLE statement of the table DoctorFacility, PatientVisit and Appointments
Show some sample data from these 3 tables. Please do post in INSERT statement like INSERT INTO DoctorFacility VALUES ( ) .....
What you have posted so far is the result of the query. We need to take a look at the data from source table
Show the expected result from the sample data in (2)