List of patients not scheduled for treatment?

[noestervemb]

Is it possible to get a list of patients that are
1) not scheduled
2) due for recall or due for some sort of treatment (most patient will be due for recall but some might not be setup correctly for recall so dont want to miss those patients)

Are going to use the list for a postcard campaign.

I can use the recall list function to get a list of people due for recall. And i can use the treatment finder to get a list of patients with treatments not scheduled. But i cant find a query that combines them? So then i am stuck with 2 excell documents and getting excell to combine but that always is tricky and i am worried it kicks someone out by mistake.

I did find this query. But the issue is is lists it as an interval. I would have to be able to select a time period. So from like 01/01/2014 to 01/01/2017 etc. I tried adding the i tried adding the SET @FromDate='2005-10-01' , @ToDate='2009-12-31';
SET @pos=0; but did not work for me. Any suggestions or workarounds or something people are already using?

/*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, 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;

[JasonL]

I couldn't find an existing query matching what you're looking for. You may want to reach out to the Query Department by using our Query Request Form on http://opendentalsoft.com:1942/ODQueryR ... tForm.aspx. Once you fill it out, you should hear back from them with either clarifying questions or a quote on what it would cost to write a custom query. Hope this helps!