Query for No FMX Recall Type

[jfdmanager]

I'm looking for a list of active patients with an appointment who do not have a recall type for an FMX. Does anyone know how I can pull this information? Thank you.

[KevinRossen]

I'm looking for a list of active patients with an appointment who do not have a recall type for an FMX. Does anyone know how I can pull this information? Thank you.

I'm assuming you're looking for future appointments? I can probably whip it together, but I need one bit of info: the RecallTypeNum for FMX. To get it, run this query:

Code: Select all

SELECT * FROM recalltype

Once you get met that, I should be able to get you the query pretty quickly.

[KevinRossen]

Well, this took a lot less time than I thought. This query will give you the PatNum, Age, and Appointment Date & Time for all patients with an appointment scheduled today or later and they don't have a recalltype of 6. You'll need to change that one item (line 4) to the appropriate recalltype. You could also use this to find anyone who hasn't had a prophy, bwx, etc just by changing the code.

Let me know if you need anything else.

Code: Select all

SELECT p.PatNum, DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(Birthdate, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(Birthdate, '00-%m-%d')) AS 'Age', a.AptDateTime
FROM patient p
LEFT JOIN appointment a ON p.PatNum=a.PatNum 
WHERE p.PatNum NOT IN (SELECT PatNum FROM recall WHERE RecallTypeNum=6) AND a.AptStatus=1 AND DATE(a.AptDateTime)>=CURDATE()
ORDER BY a.AptDateTime; 

[jfdmanager]

Thank you so much! I didn't expect a custom written report, but this is going to be very valuable to our practice.

[KevinRossen]

Thank you so much! I didn't expect a custom written report, but this is going to be very valuable to our practice.

You're welcome. It was really easy to write it up based on one of custom queries.

[Justin Shafer]

http://dynamicdentists.com/looking-for- ... a-testers/

Neat...

I know a guy named Neil Milliken and he created something VERY similar except it had a TON of charts. He had the intention of selling it, as it was actually dumping Dentrix data to a mysql database, and then that was used for creating reports. It could do it's own Time Clock, Lab Cases, etc.

I still have it, and all the code behind it..

At the end it was converted to dump Open Dental data as the office converted to OD... Then the doc sold it to another doctor who already has 2 dentrix offices..

And so MyPractice is still running in a virtual machine, in limbo.

It had a TON.. of Graphical reports.

Anyways... Want to see it??? Neil's son did most of the work.. He works at google now. MySQL and PHP and DJango I think

[Justin Shafer]

[jfdmanager]

This is great Justin. Is there a cost for the software? How do I get my hands on it?

[KevinRossen]

This is great Justin. Is there a cost for the software? How do I get my hands on it?

I spoke with the original developer not too long ago. He's retired and ceased development a few years back.