Sending one letter to each household of active patients

[DavidWolf]

Any thoughts on the best way to generate a mailing list for the households of all active patients.....

Thanks for any advice.

[brentwood]

You can find a lot of useful queries at http://70.90.133.65:1942/ODQueryList/QueryList.aspx. Here is the one I used (#35 on the list) to send out Christmas cards. You can customize it to fit your needs.


SELECT g.LName,g.FName, g.Address, g.Address2, g.City, g.State, g.Zip
FROM patient p, patient g
WHERE p.PatStatus=0
/*only patients with procedures within the last three years*/
AND EXISTS(SELECT * FROM procedurelog pl
WHERE pl.PatNum=p.PatNum
AND pl.ProcDate > CURDATE() - INTERVAL 3 YEAR) AND Length(p.Zip)>4
AND p.Guarantor=g.PatNum
GROUP BY p.Guarantor
ORDER BY LName,FName

[jclaydds]

Here is the Query I use to generate a mailing list for families that has a family member that has been seen within the last 3 years. I send out a marketing newsletter to active patients every quarter. My definition of "active" is they have been seen within the last 36 months. This value can be changed in the query to whatever you want for your definition of "active patients: This query will list one member of each family. The patient isn't necessarily the guarantor/head of household so I address the mailing as follows. If the Query returns the name as John Smith. I address the letter to The Smith Household.

Here is the query but be sure and see the comments that follow the query.

SELECT LName,FName, Address, Address2, City, State, Zip
FROM patient
WHERE PatStatus=0
/*only patients with procedures within the last number of months*/
AND EXISTS(SELECT * FROM procedurelog
WHERE procedurelog.PatNum=patient.PatNum
AND procedurelog.ProcDate > CURDATE() - INTERVAL 36 MONTH)
AND BillingType=40 AND CreditType!='X'
AND guarantor NOT IN (SELECT guarantor FROM patient WHERE CreditType = 'X')
GROUP BY Guarantor
ORDER BY LName,FName


You must do the following before running the query:

I only want to send my marketing letter to patients with a billing type of "Standard Account". I have other billing types such as Bad Account-PreCollections, Bad Account -Collections, etc. I don't want to spend my marketing dollars on mailing newsletters to people that are in collection activity so this is my reason for sending to only "Standard Accounts".

Step one: figure out the DefNum for "Standard Account". Do this by running the following query:

SELECT * FROM definition WHERE Category=4

The query above gives you the raw data for your billing types. Look in the DefNum column to find the DefNum for "Standard Account". In my data base, the value is 40 hence the query above has Billing Type=40 If your value is different, you will need to change the value accordingly.

Secondly, I have created a way to designate patients that I don't want to send a quarterly mailing. I may have seen a patient for a one-time visit. Mayber their dentist was out of town and I was covering for him/her. Maybe the patient lives out of my area and I saw them while they were visiting in my area. Maybe I don't like the patient and I am hoping they never come back. There could be a lot of reasons why I wouldn't want them to receive a mailing. In the Patient Information section, there is a area where you enter Credit Type. Open Dental suggest you use ABC but you are not limited to only these values. I use this field and I place an "X" in this field if I don't want someone to receive a mailing.

Hopefully this explains the rationale behind the language in the query.

[DavidWolf]

THANKS