Report for Unscheduled Tx

For users or potential users.
Post Reply
speeples
Posts: 27
Joined: Tue Feb 05, 2019 12:27 pm

Report for Unscheduled Tx

Post by speeples » Fri Feb 21, 2020 10:39 am

In Queries, is #50 (Treatment planned procedures, that are not in a scheduled apt ) a good report to get a date range of pt's that have unscheduled tx? Or is there a better one?
Thank you! :)

PatrickC
Posts: 56
Joined: Thu Jun 06, 2019 11:37 am

Re: Report for Unscheduled Tx

Post by PatrickC » Fri Feb 21, 2020 12:52 pm

If you are looking for an ordered range of dates, then this query will work and you can just sort by date by clicking on the Date column in the results in OD. You could also have the query order them by adding pl.ProcDate to the ORDER BY statement.

Code: Select all

ORDER BY pl.ProcDate, aptstatus, patient.LName, patient.FName ASC;
Adjusting the query to look between specific date ranges would look something like this.

Code: Select all

SET @FromDate='1880-01-01' , @ToDate='2021-02-21'; /*Adjust dates as needed*/

SELECT CONCAT(LName, ', ',FName, ' ', MiddleI) AS Patient,
pc.ProcCode AS 'Code', 
abbrdesc AS 'Description', 
ToothNum, 
DATE_FORMAT(pl.ProcDate,'%m-%d-%Y') AS 'Date', 
ap.AptStatus, 
ProcFee
FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
	AND ProcStatus=1 
	INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
	LEFT JOIN appointment ap ON pl.AptNum=ap.AptNum
WHERE (ISNULL(ap.aptnum) OR AptStatus=6 OR AptStatus=3) /*No apt, unscheduled, planned*/
AND PatStatus=0 /*patient status*/
AND pl.`ProcDate` BETWEEN @FromDate AND @ToDate
ORDER BY pl.ProcDate, aptstatus, patient.LName, patient.FName ASC;
Patrick Carlson
Open Dental Software
http://www.opendental.com

speeples
Posts: 27
Joined: Tue Feb 05, 2019 12:27 pm

Re: Report for Unscheduled Tx

Post by speeples » Wed Feb 26, 2020 10:56 am

I will try that, thank you!

Post Reply