Open Dental Forum

I'm looking for a query that provides a list of pts that were seen 6 to 8 months ago for prophies or cleanings. Ideally, it would also ID those who don't have apts for cleanings in this month... Just having a list of everyone we did cleanings on about 6 months ago would be a start.

I found a forum post for a query (below) that looked for Delta cleanings in a specific time frame. It doesn't work any more (2007 vintage). Also, rather than having to manually set the date range, I'd like it to just look 6 to 8 months in the past to find patients that might be falling thru the cracks as far as cleanings go.

SET @pos=0;
SELECT @pos:=@pos+1 as numberofpatients, carrier.CarrierName,procedurelog.ProcDate,patient.LName,patient.Fname
FROM patient,carrier,insplan,procedurelog,procedurecode
WHERE patient.PatNum=insplan.Subscriber
AND insplan.CarrierNum=carrier.CarrierNum
AND carrier.CarrierName LIKE '%Delta%'
AND patient.PatNum=procedurelog.PatNum
AND procedurelog.ProcDate>='2007-05-01'
AND procedurelog.ProcDate<='2007-09-30'
AND procedurelog.CodeNum=procedurecode.CodeNum
AND procedurecode.ProcCode LIKE '%D11%'
ORDER BY procedurelog.ProcDate

Give this a try. It finds six month adult and child prophies with no appointment scheduled.
You can manipulate the Interval to go back even further. Plus at the end you can eliminate any insurance plans if you wish.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/*Modified 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*/
/*Just change the interval values below to get the proper month*/
SET @FromDate=Date_Sub(CurDate(),Interval 180 day),@ToDate=Date_Sub(CurDate(),Interval 173 day);
SELECT p.LName,p.FName, p.email,pl.ProcDate, pc.ProcCode,CarrierName AS 'PriInsName',
(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 PatStatus = '0'
AND pl.ProcStatus=2 AND pc.ProcCode IN('D1110','D1120')
AND CarrierName not like '%Dominion%'
AND CarrierName not like '%Rayant%'
AND p.PatNum NOT IN(SELECT DISTINCT(pl.PatNum) FROM procedurelog pl
INNER JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum AND pc.ProcCode IN('D1110','D1120')
LEFT JOIN appointment a ON a.AptNum=pl.AptNum
WHERE ((pl.ProcDate>@ToDate AND pl.ProcStatus=2)
OR (a.AptStatus=1 AND a.AptDateTime>=CURDATE())))
GROUP BY pl.ProcNum
ORDER BY pl.ProcDate, p.LName,p.FName;

drtmz