Help with Query-Marketing

[jclaydds]

I need some help with a query. In Query examples, Example 35 as follows almost meets my needs.

List of families seen in the last three years. Useful for generating a list of patients for Christmas cards. After saving the resulting datafile, you would use the letter merge feature of Word to actually print the cards or labels. The first name is not very useful, since it might be anyone in the family. You would probably address it as <<LName>> Household, or something similar.

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

I am intending on using the query to generate an address list for a marketing newsletter I send out quarterly. I send it to families who I have seen within the last 3 years. I would like to further filter the above results by only sending it to patients with a billing type of "Standard Account". I don't want to spend my marketing dollars on those who are not paying me for what I have done for them in the past.

I also have a related question about using the field "Credit Type" under the Family module. In my previous practice managment software, I had the ability to define "User Codes". For example, I had a User Code "X" that I used when I didn't want to send a patient a quarterly newsletter. It might be a patient that I had seen recently (within the last 3 years) but they have moved, changed to another dentist, were from out of town and I saw them on an emergency basis but I knew they would not be returning to my practice, etc. Again, I don't want to spend my marketing dollars on patients that I know will not be returning to my practice. I was wondering if I could use the "Credit Type" field in the Family Module for the same thing. I could put an "X" in that field and then filter my query to not include anyone with an X in that field. If this is possible, what additional command(s) would I need to place in the query to omit patients with an "X" in the Credit Type field?

Thanks for your help!

[jordansparks]

Step one: figure out the DefNum for "Standard Account". Do this by running
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". Let's say, for example, it's 78. Then your query would look like this:
SELECT LName,FName, Address, Address2, City, State, Zip
FROM patient
WHERE PatStatus=0
/*only patients with procedures within the last three years*/
AND EXISTS(SELECT * FROM procedurelog
WHERE procedurelog.PatNum=patient.PatNum
AND procedurelog.ProcDate > CURDATE() - INTERVAL 3 YEAR)
AND BillingType=78
GROUP BY Guarantor
ORDER BY LName,FName

After the BillingType row, you could also add:
AND CreditType!='X'

[jwb]

We are also interested in using this kind of query for marketing purposes.
However, would it be possible to add the use of the custom "Patient Fields" definitions?
http://www.open-dent.com/manual/customp ... ields.html
Example; "Interested in Invisalign" =Y/N, then search on that field, filtered for "Y", and come up with a list useful for marketing purposes.
I have tried to write a query to get this info out of OD, without success. Any suggestions?

[jordansparks]

Yes you can do that. You're definitely venturing into power user territory. As you learn SQL, you will find about 3 ways to extract that data:
1. Cartesian join. This is the common kind with a comma. ie. SELECT ... FROM table1,table2 WHERE ...
2. LEFT JOIN. This will run faster and can include rows with no match in one of the tables.
3. Sub query. ei. SELECT field1,field2,field3,(SELECT ... FROM ....) FROM ...
So yes, there's plenty of power. You just have to decide what to do with it.