Report: patients by a specific code

[sbrugg23]

I am wondering if there is a report that can be run that will show which patients have had a specific procedure done or treatment planned? More specifically I would like to see who in the past has started ortho so I would be searching a code like D8090. Or who has had a root canal treament planned so using code D3330. Thanks.

[Tom Zaccaria]

Try this. Change the procedure code in the fourth line from the bottom to search whatever code you want and the date range where indicated.

/*264 List of procedures for a date range with patient name, service date, procedure code,
primary insurance carrier, fee, sum of ins est, sum ins paid*/

SET @FromDate='2019-1-01' , @ToDate=curdate(); /* change date here, change list of proc codes below*/
SELECT p.PatNum, pl.ProcDate, pl.ProvNum, pc.ProcCode,CarrierName AS 'PriInsName',pl.ProcFee,
(SELECT SUM(cp.InsPayEst) FROM claimproc cp WHERE cp.ProcNum=pl.ProcNum AND cp.Status IN (0,1,4)) AS '$InsPayEst',
(SELECT SUM(cp.InsPayAmt) FROM claimproc cp WHERE cp.ProcNum=pl.ProcNum AND cp.Status IN (1,4)) AS '$InsPaidAmt'
FROM procedurelog pl
INNER JOIN patient p ON p.PatNum=pl.PatNum /*just in case we need field from patient table*/
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
LEFT JOIN patplan pp ON pp.PatNum=p.PatNum AND ORDINAL=1/*by current primary insurance*/
LEFT JOIN inssub iss ON pp.InsSubNum=iss.InsSubNum
LEFT JOIN insplan ip ON ip.PlanNum=iss.PlanNum
LEFT JOIN carrier ON carrier.CarrierNum=ip.CarrierNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2 AND pc.ProcCode IN ('D0330')
AND (SELECT SUM(cp.InsPayAmt) FROM claimproc cp WHERE cp.ProcNum=pl.ProcNum AND cp.Status IN (1,4)) > 0
GROUP BY pl.ProcNum
ORDER BY pl.ProcDate, p.LName,p.FName;

drtmz

[joes]

Note that the query above only shows completed procedures that have insurance payments that sum to a value greater than zero. We do have a couple of standard reports that can find completed and treatment planned procedures by procedure code. I'll include links to the manual pages for these below.

On the Daily Procedures Report you can search for completed procedures of a specific code. It is called a "Daily" procedures report, but it can be run for a date range instead of just today's date. https://www.opendental.com/manual/reportdailyprocs.html

The Treatment Finder Report can find treatment planned procedures of a specific code or code range. https://www.opendental.com/manual/repor ... inder.html