Overdue Hygiene Query

For users or potential users.
Post Reply
johnpappas
Posts: 1
Joined: Thu Nov 04, 2021 12:31 pm

Overdue Hygiene Query

Post by johnpappas » Thu Nov 04, 2021 12:37 pm

I need a query that is for:
  • for active hygiene patients in a date range
  • who are overdue for hygiene and
  • have no scheduled appointments
This seems like an easy one but I can't find anything close!

Thank you!

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Overdue Hygiene Query

Post by Tom Zaccaria » Fri Nov 05, 2021 5:14 am

Been using this forever. Change the procedure codes to search for whatever you want.
Time interval is in days to search.

drtmz

/*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 177 day),@ToDate=Date_Sub(CurDate(),Interval 173 day);
SELECT p.LName,p.FName, p.email, p.wirelessphone, PreferRecallMethod as 'Rcl', pl.ProcDate, pc.ProcCode,CarrierName AS 'PriInsName'
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 p.email, pl.ProcDate, p.LName,p.FName;

Post Reply