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!
Report for Unscheduled Tx
Re: Report for Unscheduled Tx
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.
Adjusting the query to look between specific date ranges would look something like this.
Code: Select all
ORDER BY pl.ProcDate, aptstatus, patient.LName, patient.FName ASC;
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;
Re: Report for Unscheduled Tx
I will try that, thank you!