Report for Unscheduled Tx

[speeples]

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]

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;

[speeples]

I will try that, thank you!