Notifications
Clear all
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
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
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
