Unscheduled Hygenie SQL does not

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
Mikol22
Posts: 3
Joined: Tue Jun 08, 2021 3:47 pm

Unscheduled Hygenie SQL does not

Post by Mikol22 » Tue Jun 08, 2021 7:41 pm

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

joes
Posts: 165
Joined: Tue Aug 13, 2019 12:41 pm

Re: Unscheduled Hygenie SQL does not

Post by joes » Thu Jun 10, 2021 10:59 am

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
);
Joe Sullivan
Open Dental Software
http://www.opendental.com

Post Reply