Welcome to the NEW CHUG— your ultimate destination for cutting-edge insights and discussions at the intersection of healthcare and technology.

Our platform is dedicated to exploring the dynamic landscape of healthcare technology, empowering professionals, enthusiasts, and innovators alike with the knowledge and resources to navigate the rapidly evolving field.

From telemedicine revolutionizing patient care to the transformative potential of artificial intelligence in diagnostics, we delve into a myriad of topics shaping the future of healthcare delivery. Whether you’re a seasoned healthcare IT professional, an aspiring entrepreneur in the digital health space, or simply curious about the latest innovations, our curated content, expert interviews, and thought-provoking articles provide a comprehensive exploration of healthcare technology’s most pressing issues.

Join us as we navigate the forefront of innovation, driving forward the transformation of healthcare for a healthier, more connected world.

Have questions or suggestions? Please use the contact button below.

Notifications

Clear all

Appointments By Patient and Insurance Carrier

Trading Post

Last Post by Anonymous 12 years ago

3 Posts

2 Users

0 Reactions

57 Views

RSS

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

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

24 Forums
9,940 Topics
39.9 K Posts
0 Online
6,470 Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed