Help me edit an existing Query Example
Posted: Wed Jan 07, 2015 7:51 pm
My practice management consultant wants me to pull a treatment plan report that lists all treatment plans without scheduled appts, excluding certain codes. After spending a lot of time perusing the query examples, #233 seems like it will suit our needs. However, it doesn't total the fees at the bottom of the report. Is there something I can add to this string to give me a grand total at the end? Keep in mind that I have no coding experience so I would need you to tell me what to add specifically. Thanks!
/*233 Treatment planned procedures with date and fee for patients without a planned or scheduled appointment, excludes codes in user defined list*/
SELECT patient.PatNum, pl.DateTP,pc.ProcCode, pc.AbbrDesc, pl.ProcFee
FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum AND ProcStatus=1/*treatment planned*/
WHERE (SELECT COUNT(ap.PatNum) FROM appointment ap WHERE patient.PatNum=ap.PatNum AND
(ap.AptStatus=1 OR ap.AptStatus=6 ))=0 /*no planned or scheduled appt*/
AND patient.PatStatus=0 /*Active patients*/
AND pc.ProcCode NOT IN('D0120', 'D0150','D1120', 'D1110','D1203','D1204', 'D1206', 'D0272','D0274')/*Edit this list as needed, these codes are excluded*/
ORDER BY patient.LName, patient.FName, pl.DateTP, pc.ProcCode ASC;
/*233 Treatment planned procedures with date and fee for patients without a planned or scheduled appointment, excludes codes in user defined list*/
SELECT patient.PatNum, pl.DateTP,pc.ProcCode, pc.AbbrDesc, pl.ProcFee
FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum AND ProcStatus=1/*treatment planned*/
WHERE (SELECT COUNT(ap.PatNum) FROM appointment ap WHERE patient.PatNum=ap.PatNum AND
(ap.AptStatus=1 OR ap.AptStatus=6 ))=0 /*no planned or scheduled appt*/
AND patient.PatStatus=0 /*Active patients*/
AND pc.ProcCode NOT IN('D0120', 'D0150','D1120', 'D1110','D1203','D1204', 'D1206', 'D0272','D0274')/*Edit this list as needed, these codes are excluded*/
ORDER BY patient.LName, patient.FName, pl.DateTP, pc.ProcCode ASC;