Hello
I am trying to understand how OpenDental does commands. Right now I am trying to get all patients that do not have a scheduled hygenie. I came up with this SQL code: https://pastebin.com/eT61313L
It does not seem to be correct though and i am not sure why. Is there something about OpenDental am i not understandin
Unscheduled Hygenie SQL does not
Re: Unscheduled Hygenie SQL does not
A few modifications gives you the query below which will create a list of all patients that don't have a hygiene appointment scheduled within a given date range. Note that I added a PatStatus filter so that only active patients show in the list. If you are developing a query for the purpose of tracking which patients need to be scheduled for hygiene appointments, you may want to look into our Recall system which can generate a list of patients due for such appointments.
Here is a link to our Recall manual page: https://www.opendental.com/manual/recall.html
And here is a link to our Recall Webinar videos: https://www.youtube.com/playlist?list= ... fbFFPDBco8
/*Change dates between the ' ' in format 'YYYY-mm-dd'*/
SET @FromDate='2021-06-10' , @ToDate='2021-06-10';
SELECT patient.PatNum, patient.FName, patient.LName
FROM patient
WHERE patient.PatStatus=0
AND NOT EXISTS (
SELECT appointment.PatNum
FROM appointment
WHERE DATE(appointment.AptDateTime) BETWEEN @FromDate AND @ToDate
AND appointment.IsHygiene = 1
AND patient.PatNum=appointment.PatNum
);
Here is a link to our Recall manual page: https://www.opendental.com/manual/recall.html
And here is a link to our Recall Webinar videos: https://www.youtube.com/playlist?list= ... fbFFPDBco8
/*Change dates between the ' ' in format 'YYYY-mm-dd'*/
SET @FromDate='2021-06-10' , @ToDate='2021-06-10';
SELECT patient.PatNum, patient.FName, patient.LName
FROM patient
WHERE patient.PatStatus=0
AND NOT EXISTS (
SELECT appointment.PatNum
FROM appointment
WHERE DATE(appointment.AptDateTime) BETWEEN @FromDate AND @ToDate
AND appointment.IsHygiene = 1
AND patient.PatNum=appointment.PatNum
);