Open Dental Forum

for our reactivation campaign. We would need to be able to export this list with the patients address to send them a postcard. We only want patients who are on some sort of ppo and medicaid (so not our patients without insurance).

Is this possible?

Looks like you want some kind of recall query.
Does this help? Change the date range in line 5.

/*1026 Recall info for patients due in date range that don't have any future scheduled appointment. Only shows patients with any insurance carrier, showing date of last visit*/
/*Change dates as needed in second line of query */
/*Query code written/modified: 11/17/2014*/
SET @pos=0;
SET @StartDate='2016-07-01' , @EndDate='2017-02-01';
SELECT @pos:=@pos+1 AS 'Count',p.LName, p.FName, r.DateDue, d.ItemName AS 'RecallStatus',rt.Description AS 'Type',
( SELECT CONVERT(DATE_FORMAT(MAX(ProcDate),'%m/%d/%Y'), CHAR)
FROM patient
INNER JOIN procedurelog ON procedurelog.PatNum=patient.PatNum
WHERE procedurelog.ProcStatus=2 AND patient.PatStatus=0 AND p.PatNum=patient.PatNum
GROUP BY procedurelog.PatNum
) AS 'LastVisit(any)', c.CarrierName
FROM patient p
INNER JOIN patplan pp ON pp.PatNum=p.PatNum
INNER JOIN inssub ib ON ib.InsSubNum=pp.InsSubNum
INNER JOIN insplan ip ON ip.PlanNum=ib.PlanNum
INNER JOIN carrier c ON c.CarrierNum=ip.CarrierNum
INNER JOIN recall r ON p.PatNum=r.PatNum
INNER JOIN recalltype rt ON rt.RecallTypeNum=r.RecallTypeNum
LEFT JOIN definition d ON r.RecallStatus=d.DefNum
WHERE (DateDue BETWEEN @StartDate AND @EndDate)
AND NOT EXISTS (
SELECT a.PatNum
FROM appointment a
WHERE DATE(a.AptDateTime)>=CURDATE()
AND a.AptStatus=1
AND a.PatNum=p.PatNum
)
ORDER BY p.LName, p.FName;

drtmz

youre a coding genius tom thankyou. Can we have it pull their address too? We are trying to send out postcards

Thanks for the superlative. This is a modification of query #1026 from OD's query examples page. Not my code.
Try this:

/*1026 Recall info for patients due in date range that don't have any future scheduled appointment. Only shows patients with any insurance carrier, showing date of last visit*/
/*Change dates as needed in second line of query */
/*Query code written/modified: 11/17/2014*/
SET @pos=0;
SET @StartDate='2016-07-01' , @EndDate='2017-02-01';
SELECT @pos:=@pos+1 AS 'Count',p.LName, p.FName, p.address, p.city ,p.state, p.zip, r.DateDue, d.ItemName AS 'RecallStatus',rt.Description AS 'Type',
( SELECT CONVERT(DATE_FORMAT(MAX(ProcDate),'%m/%d/%Y'), CHAR)
FROM patient
INNER JOIN procedurelog ON procedurelog.PatNum=patient.PatNum
WHERE procedurelog.ProcStatus=2 AND patient.PatStatus=0 AND p.PatNum=patient.PatNum
GROUP BY procedurelog.PatNum
) AS 'LastVisit(any)', c.CarrierName
FROM patient p
INNER JOIN patplan pp ON pp.PatNum=p.PatNum
INNER JOIN inssub ib ON ib.InsSubNum=pp.InsSubNum
INNER JOIN insplan ip ON ip.PlanNum=ib.PlanNum
INNER JOIN carrier c ON c.CarrierNum=ip.CarrierNum
INNER JOIN recall r ON p.PatNum=r.PatNum
INNER JOIN recalltype rt ON rt.RecallTypeNum=r.RecallTypeNum
LEFT JOIN definition d ON r.RecallStatus=d.DefNum
WHERE (DateDue BETWEEN @StartDate AND @EndDate)
AND NOT EXISTS (
SELECT a.PatNum
FROM appointment a
WHERE DATE(a.AptDateTime)>=CURDATE()
AND a.AptStatus=1
AND a.PatNum=p.PatNum
)
ORDER BY p.LName, p.FName;

drtmz

SELECT @pos:=@pos+1 AS 'Count',p.LName, p.FName, p.address, p.city ,p.state, p.zip, p.Email, p.WirelessPhone, r.DateDue, d.ItemName AS 'RecallStatus',rt.Description AS 'Type',

=======================================================================================================
replace this line in the above query and you will get the patients emails and cell phone numbers too.
======================================================================================================

Now you have the option to email your patients or text message them for free.

Export your file as an EXCEL file
Delete all the columns , and keep the email addresses only.
Save the file as CSV text file.
in Gmail you can create a Group
Import the emails into Gmail Group
Send an Email to the Group

Gmail contacts link
https://www.google.com/contacts/u/0/?cplus=1#contacts

or you can use many other ways to send emails to a group
*****do not send more than 500 emails per hour.

==============================================================================================

to send text messages to a group, you need to have all the cell numbers separated by a comma.

you can create your own FREE Google Voice account.
and go to this webpage
https://www.google.com/voice/b/0?gsessi ... GdNL#inbox
click on "text"
and enter 5 phone numbers at a time (not more), and your message.
if you send more than 20-30 ?? text messages per minute Google Voice may block you.
so send it slowly. it may take you 15 minutes to send all the text messages.

=================================================================================================
or you can purchase a software that will do that automatically