Page 1 of 1
Query for No FMX Recall Type
Posted: Tue Jun 24, 2014 1:02 pm
by 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.
Re: Query for No FMX Recall Type
Posted: Tue Jun 24, 2014 9:56 pm
by KevinRossen
jfdmanager wrote: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:
Once you get met that, I should be able to get you the query pretty quickly.
Re: Query for No FMX Recall Type
Posted: Tue Jun 24, 2014 10:23 pm
by 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;
Re: Query for No FMX Recall Type
Posted: Wed Jun 25, 2014 8:17 am
by jfdmanager
Thank you so much! I didn't expect a custom written report, but this is going to be very valuable to our practice.
Re: Query for No FMX Recall Type
Posted: Wed Jun 25, 2014 9:41 am
by KevinRossen
jfdmanager wrote: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.
Re: Query for No FMX Recall Type
Posted: Sat Jun 28, 2014 7:51 am
by 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
Re: Query for No FMX Recall Type
Posted: Sat Jun 28, 2014 8:37 am
by Justin Shafer
Re: Query for No FMX Recall Type
Posted: Tue Aug 12, 2014 11:13 am
by jfdmanager
This is great Justin. Is there a cost for the software? How do I get my hands on it?
Re: Query for No FMX Recall Type
Posted: Tue Aug 12, 2014 1:52 pm
by KevinRossen
jfdmanager wrote: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.
