Open Dental Forum

Looking to reactivate old patients who have not been seen in the pass two years. Need last visit date, insurance type, name, address, and to be able to print on labels. Do you know if this report exist or do I need to request a
custom report? Thank you!

http://opendentalsoft.com:1942/ODQueryL ... yList.aspx
query #601 and #78 modified
/*601 List of patients who have had their first and last visits in a date range and don't have any other scheduled appts in the future*/
just change the dates
*78 Active (status) patients with no scheduled apt who have not been in for a time period with the date of their last completed apt.
====================================================================================================

1- Export the list AS AN EXCEL DOCUMENT,
2- use Microsoft Word to create the labels
3- Create Mailing Labels in Word using Mail Merge from an Excel Data Set
YOUTUBE video tutorial:
https://www.youtube.com/watch?v=jNEyvGHvlpk
jump to the time 2:30 to make the labels in Word

A NO COST OPTION - FREE
this list will have also their email and cell phone numbers if you want to send them emails or text them.
include inactive patients
===========================================================================================

/*601 List of patients who have had their first and last visits in a date range and don't have any other scheduled appts in the future*/
SET @pos=0, @FromDate='2015-01-01' , @ToDate='2017-06-30';
SELECT P.LName,p.FName,p.Address,p.Address2,p.City,p.State,p.Zip,p.PatNum,p.Email,p.WirelessPhone, A.DateFirstVisit, B.DateLastVisit /*, C.DateNextApt*/
FROM patient p
INNER JOIN (SELECT PatNum, MIN(pl.ProcDate) AS DateFirstVisit
FROM procedurelog pl
WHERE pl.ProcStatus=2 GROUP BY pl.PatNum HAVING MIN(pl.ProcDate) BETWEEN @FromDate AND @ToDate)A
ON p.PatNum=A.PatNum
INNER JOIN (SELECT PatNum, MAX(pl.ProcDate) AS DateLastVisit
FROM procedurelog pl
WHERE pl.ProcStatus=2 GROUP BY pl.PatNum HAVING MIN(pl.ProcDate) BETWEEN @FromDate AND @ToDate)B
ON p.PatNum=B.PatNum
LEFT JOIN (SELECT ap.PatNum,MIN(DATE(ap.AptDateTime)) AS DateNextApt FROM appointment ap WHERE DATE(ap.AptDateTime)>=CURDATE() AND ap.AptStatus IN(1,3,4) /*sched, unsched list,ASAP list*/) C ON p.PatNum=C.PatNum
WHERE ISNULL(DateNextApt);

/*78 Active (status) patients with no scheduled apt who have not been in for a time period with the date of their last completed apt. - (From @daysIntervalStart days ago to @daysIntervalEnd days ago, change the interval currently 365 days: 1 year) This is useful for making a patient list before archiving patients, to call and try one last time.*/
SET @pos=0, @daysIntervalStart=365/*<<<Just normally change this one*/, @daysIntervalEnd=0;
SELECT @pos:=@pos+1 as numberofpatients,A.* FROM
(SELECT patient.PatNum, patient.HmPhone, patient.address, patient.city, patient.state, patient.zip, patient.Email, patient.WirelessPhone, tmp2.AptDateTime AS LastApt,
(TO_DAYS(CURDATE()) - TO_DAYS(tmp2.AptDateTime)) AS 'DaysSince'
FROM patient
LEFT JOIN (SELECT DISTINCT PatNum FROM appointment WHERE AptStatus=1)/*patients with scheduled appointments*/ tmp1 ON patient.PatNum=tmp1.PatNum
LEFT JOIN (SELECT PatNum, MAX(AptDateTime) AS 'AptDateTime' FROM appointment WHERE AptStatus=2 GROUP BY PatNum) tmp2 ON patient.PatNum=tmp2.PatNum
WHERE tmp1.PatNum IS NULL

AND ((TO_DAYS(CURDATE()) - TO_DAYS(tmp2.AptDateTime)) BETWEEN @daysIntervalEnd AND @daysIntervalStart)
AND patient.PatStatus=0
GROUP BY tmp2.PatNum
ORDER BY patient.LName, patient.FName ASC)A;

Thank you so much for your help! Will give and update on Monday of my results .