List of hygiene appointments with "wrong" associated provide

For users or potential users.
Post Reply
noestervemb
Posts: 59
Joined: Sat Jul 09, 2016 7:45 am

List of hygiene appointments with "wrong" associated provide

Post by noestervemb » Tue Apr 11, 2017 2:19 pm

We bought a dental clinic and hired the selling dentist so we are 3 dentist right now. On a lot of our patients the association between primary provider and who they see is still not completely fixed. We fixed most of them manually but we still find some that are mismatched. It would be awesome if i could run a query that would show me patients with a scheduled hygiene appointment with a provider different than their primary assigned provider. We book all of our hygiene appointments with the dentist as primary provider so it shows our color so we know which checks to do. Does anybody know if that is avaliable already or i have to get a custom query made? Then i could check them individually and then fix the remaining ones.

User avatar
dgraffeo
Posts: 147
Joined: Wed Sep 24, 2014 3:19 pm

Re: List of hygiene appointments with "wrong" associated pro

Post by dgraffeo » Tue Apr 11, 2017 3:38 pm

This may be adequate:
SELECT patient.PatNum,patient.LName,patient.FName, appointment.AptDateTime FROM appointment INNER JOIN patient ON appointment.PatNum=patient.PatNum INNER JOIN provider ON patient.PriProv=provider.ProvNum WHERE appointment.ProvNum!=provider.ProvNum AND appointment.IsHygiene=TRUE;

It returns the appointment date (and patient information) for appointments that are marked as hygiene and has the provider set to other than the patient's primary provider.
"To understand what recursion is, you must first understand recursion."

David Graffeo
Open Dental Software
http://www.opendental.com

noestervemb
Posts: 59
Joined: Sat Jul 09, 2016 7:45 am

Re: List of hygiene appointments with "wrong" associated pro

Post by noestervemb » Wed Apr 12, 2017 6:35 am

Close, but it gives me only the appointments that are marked hygiene. We dont mark hygiene appointments as use hygiene color because we want the providers color to show up. We use operatory number instead so we know if they are in the hygiene op it is a hygiene appointment. If i change it to the query to "FALSE" so

SELECT patient.PatNum,patient.LName,patient.FName, appointment.AptDateTime FROM appointment INNER JOIN patient ON appointment.PatNum=patient.PatNum INNER JOIN provider ON patient.PriProv=provider.ProvNum WHERE appointment.ProvNum!=provider.ProvNum AND appointment.IsHygiene=FALSE;


It gives me a list of all appointments scheduled with not the patients primary provider. I would just need the ability to somehow specify only in certain operatories so somehow add to this query "Operatory1, operatory4" and then it would only show the appointments for operatory i selected. Is that possible?

User avatar
dgraffeo
Posts: 147
Joined: Wed Sep 24, 2014 3:19 pm

Re: List of hygiene appointments with "wrong" associated pro

Post by dgraffeo » Wed Apr 12, 2017 8:21 am

Indeed. When you say "It's a hygiene appointment" around here, we take that to mean that you've checked the "Is Hygiene" checkbox in appointments, so that's what I went off of :)
Here is the query modified to look for specific operatories:

SELECT patient.PatNum,patient.LName,patient.FName, appointment.AptDateTime FROM appointment INNER JOIN patient ON appointment.PatNum=patient.PatNum INNER JOIN provider ON patient.PriProv=provider.ProvNum INNER JOIN operatory ON appointment.Op=operatory.OperatoryNum WHERE appointment.ProvNum!=provider.ProvNum AND operatory.OpName IN('Operatory1','operatory4');

Assuming that the operatory's names are what you're looking at (and not the abbreviation) the above query should show you appointments in operatory4 and Operatory1 whose provider is different than the patient's primary provider. Change the operatory names if you need to. This will also show all appointments where this happens, not just scheduled ones. If you want only scheduled ones, add:
AND appointment.AptStatus=1
just prior to the ending semicolon, making sure there is a space on both sides of "AND".
"To understand what recursion is, you must first understand recursion."

David Graffeo
Open Dental Software
http://www.opendental.com

noestervemb
Posts: 59
Joined: Sat Jul 09, 2016 7:45 am

Re: List of hygiene appointments with "wrong" associated pro

Post by noestervemb » Wed Apr 12, 2017 2:07 pm

That is awesome! Thank you so much.

Post Reply