Notifications
Clear all
Topic starter
Hello, I am writing a SQL query based on appointments, both future and past. The issue I am having is that the output seems to repeat. I have been having this issue when just joing the three tables of Appointments, PatientProfile, and DoctorFacility. For one patient I will get the output of 2000+ rows for a given patient, and the appointments appearing on each weekday. I am not sure if the issue is with my joins or just with my data. Has anyone else had this issue, or have an idea of what I might be doing wrong? Thanks below is my SQL Query for help.
Select Distinct
pp.last,
RTRIM(RTRIM(pp.Last) + ', ' + RTRIM(pp.First)) AS PatientName,
Convert(Varchar(11),a.ApptStart) as [Appt Start],
'Eligibility Verified' = CASE
When ppi.EligibilityVerified <> 0 then 'VERIFIED'
Else 'NOT VERIFIED'
End,
ppi.EligibilityVerifiedDate,
isnull(ppi.EligibilityVerifiedBy,'NOT VERIFIED') as [Insurance Verified By],
Isnull(Convert(Varchar(12),ppi.EligibilityVerifiedDate), 'NOT VERIFIED') as [Eligibility Verified Date],
EligibilityVerifiedFromAppointmentID,
df.listname as [Doctor],
ic.listname as [Insurance Carrier],
'Insurance Class' = CASE
When ppi.orderforclaims = 1 then 'Primary'
Else 'Secondary'
End
From PatientInsurance ppi
Join PatientProfile pp on pp.PatientProfileId = ppi.PatientProfileId
Join DoctorFacility df on pp.PrimaryCareDoctorId = df.DoctorFacilityId
Join Appointments a on a.DoctorId = pp.PrimaryCareDoctorId
Join InsuranceCarriers ic on ppi.InsuranceCarriersId = ic.InsuranceCarriersId
Order by pp.last
Posted : August 27, 2012 2:22 am
You are not joining individual patients to multiple appointments. You are joining multiple patients to multiple appointments by joining the patients' primary care doc to the appointment doc. This results in the duplication of patients having the same appointment. Remove your current Appointments join and join the PatientProfile table to the Appointments table as below:
JOIN Appointments a ON pp.PatientProfileId = a.OwnerId
The OwnerId field is the foreign key to the PatientProfile table when a.ApptKind = 1. Typically, you should add to the WHERE statement to reflect that after the joins and before the ORDER BY.
WHERE a.ApptKind = 1
Or I guess you could add the a.ApptKind = 1 to the join above.
JOIN Appointments a ON pp.PatientProfileId = a.OwnerId AND a.ApptKind = 1
If you are looking for the patients' primary care doc and appointment doc the following joins should provide that:
FROM PatientProfile pp
JOIN Appointments a ON pp.PatientProfileId = a.OwnerId
JOIN DoctorFacility dfd ON a.DoctorId = dfd.DoctorFacilityId /*This is the doctor on the scheduled appointment*/
JOIN DoctorFacility dfpcp ON pp.PrimaryCareDoctorId = dfpcp.DoctorFacilityId /*This is the primary care doc from patient registration*/
Since you are joining on InsuranceCarriers as well, you will have duplication if the patient has multiple insurance carriers. There will be one result for primary, one for secondary, etc. You will not have duplication of multiple patients having the same appointments.
Posted : September 10, 2012 2:04 am
Topic starter
Hello thanks for your reply, I just figured it out last week, I didn't know the ownerid was the foreign key from the patient profile table, what threw me off was the naming scheme change not sure why it changed but it threw me off.
I really appreciate the response.
Posted : September 10, 2012 2:25 am
