right now the treatment finder allows us to print and mail out "End of the year benefits"/"Use it or loose it" letter. It would be nice if we can email to all of our patients this letter, rather than having to waste paper, time and stamps and more time or lost mail.
Raj Ghuman
need the ability to email, not mail, end of the year benefit
Re: need the ability to email, not mail, end of the year ben
Vote / pledge / help promote request #2291
http://www.opendental.com/manual/featurerequests.html
http://www.opendental.com/manual/featurerequests.html
The best thing about a boolean is even if you are wrong, you are only off by a bit.
Jason Salmon
Open Dental Software
http://www.opendental.com
Jason Salmon
Open Dental Software
http://www.opendental.com
Re: need the ability to email, not mail, end of the year ben
Code: Select all
/*356 List of Patients that have not been in since a specific date that have insurance benefits remaining.
Includes Insurance Carrier's Name, Patient's Name, Three Phone Numbers, Benefits Remaining,
TP Remaining and Last seen date. Assumes everyone has calendar year benefits*/
SET @FromDate='2017-12-28';
SELECT p.LName,
p.FName,
p.HmPhone,
p.WkPhone,
p.WirelessPhone,
p.Email,
DATE_FORMAT(lastseen.LastSeen,'%m/%d/%Y')AS LastSeen,
annualmax.AnnualMax '$AnnualMax_',
used.AmtUsed '$AmountUsed_',
annualmax.AnnualMax-COALESCE(used.AmtUsed,0) '$AmtRemaining_',
c.CarrierName
FROM patient p
INNER JOIN patplan ON p.PatNum=patplan.PatNum
INNER JOIN inssub ON inssub.InsSubNum=patplan.InsSubNum
INNER JOIN insplan ip ON ip.PlanNum=inssub.PlanNum
INNER JOIN carrier c ON c.CarrierNum=ip.CarrierNum
INNER JOIN (
SELECT benefit.PlanNum,
MAX(benefit.MonetaryAmt) AS AnnualMax
FROM benefit
LEFT JOIN covcat ON covcat.CovCatNum = benefit.CovCatNum
WHERE benefit.BenefitType = 5 /* limitation */
AND benefit.TimePeriod = 2 /* calendar year */
AND (covcat.EbenefitCat=1 OR ISNULL(covcat.EbenefitCat))
AND benefit.MonetaryAmt > 0 /* (-1) indicates empty */
GROUP BY benefit.PlanNum
) annualmax ON annualmax.PlanNum=inssub.PlanNum
LEFT JOIN (
SELECT patplan.PatPlanNum,
SUM(claimproc.InsPayAmt) AS AmtUsed
FROM claimproc
INNER JOIN inssub ON claimproc.InsSubNum=inssub.InsSubNum
INNER JOIN patplan ON inssub.InsSubNum=patplan.InsSubNum
AND patplan.PatNum=claimproc.PatNum
WHERE claimproc.Status IN (1, 3, 4) -- Rec, Adj, Supp.
AND YEAR(claimproc.ProcDate)=YEAR(CURDATE())
AND claimproc.InsPayAmt!=0
GROUP BY patplan.PatPlanNum
) used ON used.PatPlanNum=patplan.PatPlanNum
INNER JOIN (
SELECT p.PatNum,
MAX(procdate) AS LastSeen
FROM patient p
INNER JOIN procedurelog pl ON pl.PatNum=p.PatNum
AND pl.ProcStatus=2 /*complete*/
AND p.PatStatus=0 /*active patient*/
GROUP BY pl.PatNum
) lastseen ON lastseen.PatNum=p.PatNum AND lastseen.LastSeen<@FromDate
WHERE PatStatus=0
ORDER BY c.CarrierName;
1- run the query, export to excel, go through the list and delete what you don't need,
2- keep the Last names, first names, phone numbers, and emails. Save the file as a text file
3- In Google Contacts, create a separate group, and import your text file to this group , ( patients info)
https://www.google.com/contacts/u/0/?cplus=0#contacts
4- Send an email to your group now
5- Don't send more than 200 or 300 emails per hour. you may be penalized by your email provider. check with them before you send your marketing bulk emails
6- You can call any patient that does not have any email
===================================================================================================================================================
We use a special program that extract all the emails, and does all of that automatically, including sending the emails through our personal email account
We use a group email software.
Re: need the ability to email, not mail, end of the year ben
Another alternative - Use a plugin from http://www.hrdsq.com. They provide real time insurance verification also - so one can have more updated and accurate info before sending.
It helped us mine more than above 100K remaining on charts with our patients, based on this we changed our recall strategy. Already booked 64K out of it.
It helped us mine more than above 100K remaining on charts with our patients, based on this we changed our recall strategy. Already booked 64K out of it.