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;