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

SQL Script Pulling ApptStart Status ApptStatusMId and DoctorId

MEL, SQL, Oracle

Last Post by Anonymous 1 second ago

3 Posts

2 Users

0 Reactions

71 Views

RSS

tstevens0426

(@tstevens0426)

Posts: 65

Trusted Member

Topic starter

select p.searchname as PatientName,p.PatientId,oh.NAME as ObsTerm,o.OBSDATE as ObsDate,o.obsvalue
from OBS o
left join OBSHEAD oh on o.hdid = oh.HDID
left join PatientProfile p on p.PID = o.PID
left join vPatientAppointments a on a.PId = p.PId
left join DoctorFacility d on d.DoctorFacilityId = a.DoctorId
where oh.NAME in ('INSTRUCTIONS','PI OPHTH')
and d.DotId = 'doctor id goes here'
and a.ApptStart between '2019-05-20' and '2019-05-24'

then I have to run this for every single patient ID that generates from above
select *
from Appointments ap
inner join PatientProfile pp on ap.OwnerId = pp.PatientProfileId
where pp.PatientId = 'patient ID goes here'
I want to add something like this that would give me the ApptStart for date of the appointment
and ApptStart is shown/generated
and ApptStatusMId is can be filtered as I don't want to see canceled, rescheduled, or completed
and DoctorId 'doctor id goes here'
Any SQL help would be appreciated. Thank you.

Posted : May 10, 2019 3:34 am

jbarta

(@jbarta)

Posts: 12

Active Member

If I understand your need, it appears you are running the first query to get the patients to run in the second query. I would do something like the query below where you do a sub-query inside the WHERE clause. Then you just join the Appointment table to the MedLists table to get the appointment status.

select *
,m.Description AS AppointmentStatus
from Appointments ap
inner join PatientProfile pp on ap.OwnerId = pp.PatientProfileId
LEFT JOIN MedLists m on ap.ApptStatusMId = m.MedListsId AND m.TableName = 'AppointmentStatus'
where
--Start of Subquery
pp.PatientId IN (
select Distinct p.PatientProfileId
from OBS o
left join OBSHEAD oh on o.hdid = oh.HDID
left join PatientProfile p on p.PID = o.PID
left join vPatientAppointments a on a.PId = p.PId
left join DoctorFacility d on d.DoctorFacilityId = a.DoctorId
where oh.NAME in ('INSTRUCTIONS','PI OPHTH')
and d.DotId = --DoctorId goes here
and a.ApptStart between '2019-05-20' and '2019-05-24')
--End of Subquery

Hopefully, this helps.

Posted : May 13, 2019 9:23 am

jbarta

(@jbarta)

Posts: 12

Active Member

Sorry, there were a few mistakes in my query.

select *
,m.Description AS AppointmentStatus
from Appointments ap
inner join PatientProfile pp on ap.OwnerId = pp.PatientProfileId
LEFT JOIN MedLists m on ap.ApptStatusMId = m.MedListsId AND m.TableName = 'AppointmentStatus'
where
--Start of Subquery
pp.PatientProfileId IN (
select Distinct p.PatientProfileId
from OBS o
left join OBSHEAD oh on o.hdid = oh.HDID
left join PatientProfile p on p.PID = o.PID
left join vPatientAppointments a on a.PId = p.PId
left join DoctorFacility d on d.DoctorFacilityId = a.DoctorId
where oh.NAME in ('INSTRUCTIONS','PI OPHTH')
and d.DotId = --DoctorId goes here
and a.ApptStart between '2019-05-20' and '2019-05-24')
--End of Subquery

Posted : May 13, 2019 9:36 am

Share:

24 Forums
9,940 Topics
39.9 K Posts
5 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

Powered by wpForo Powered by wpForo version 2.4.0