Open Dental Forum

Can anyone suggest a query that will print out a summary $ total of uncscheduled treatment plans, the date of the plan(s), along with patient name and phone number?

Something like:

Patient name.........Unscheduled TP...........TP date.............Patient phone #
Joe Smith.................$5300.00 ...............01/01/2008...........817-555-1111
Sandy Jones..............$4722.00................02/20/2008...........817-555-1112
Darren Johns.............$ 900.00.................02/23/2008...........817-555-0007
...

Oh, and TPs equaling $0 should be excluded.

Here is something I have written up for you, but I have not been able to test it out as I am using my laptop with a limited database. Try it out and see if it is what you need.


CREATE temporary table tp
SELECT patnum, (sum(procfee))as $Unscheduled, Date_format(datetp,'%m/%d/%Y') as TP_Date from procedurelog
WHERE (procstatus)=1
GROUP by datetp;
SELECT patient.patnum, $Unscheduled, TP_Date, HMphone as Phone from patient
left join tp on tp.patnum=patient.patnum
WHERE $Unscheduled>0
Group by patient.patnum

Great, thanks James, that works.

If you have time, could you add the ability to show only certain date ranges?

Thanks, I owe you one.....

Here is report with date option. I had also realized that the previous report was giving you treatment planned items that may be scheduled. This one will not include treatment planned and scheduled, only treatment planned and unscheduled.

Set @StartDate='03/01/2008', @EndDate='04/31/2008';

CREATE temporary table tp
SELECT patnum, (sum(procfee))as $Unscheduled, Date_format(datetp,'%m/%d/%Y') as TP_Date from procedurelog
WHERE (procstatus)=1
AND aptnum=0
GROUP by datetp;
SELECT patient.patnum, $Unscheduled, TP_Date from patient
left join tp on tp.patnum=patient.patnum
WHERE $Unscheduled>0
AND TP_Date>@StartDate
AND TP_Date<@EndDate
Group by patient.patnum

Thank you very much.

That has weeded out the $0 treatment plans very nicely. I'm still seeing treatment plans that date back to 2001 though.

But, if I change it to the following, it does what I want:
Thank you again for you help!