Query Help

[msmfd]

Hi All,
Looking for two queries:
List of Patients Overdue for Recall
Hygiene Pre- Appointment %

I have not been successful in searching the OD query list for these, just wondering if someone else has them already.

Thank you in advance for any help you may have to offer!

[Tom Zaccaria]

Try this. Change the Interval days to fit your needs for the first query.
Not sure about your second request.

/*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 175 day);
SELECT p.LName,p.FName,
substring(p.wirelessphone,1,20) as 'Cell Phone',
substring(p.email,1,23) as 'Email',
substring(PreferRecallMethod,1,2) as 'R',
substring( pl.ProcDate,1,10) as 'PDate',
substring(pc.ProcCode,1,5)as Proc,
CarrierName AS 'PriInsName',
(SELECT SUM(cp.InsPayAmt) FROM claimproc cp WHERE cp.ProcNum=pl.ProcNum AND cp.Status IN (1,4)) AS '$InsPa'
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 CarrierName not like '%DMO%'
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;

drtmz

[msmfd]

Thank you, I can't wait to try it out!