SQL Queries for Goldmine 1

[JClinchard]

My boss wants to periodically email his contacts, once a quarter. What he wants to avoid more than anything is sending an email to a contact twice or three times in a quarter.

The search I am trying to build would search the history field of a contact to see if they have been sent an email with 'X' subject line, and if so, group them with other like candidates who have recieved the email, so that we don't duplicate our emails.

Is this kind of search possible? Also, is there a decent tutorial available, or a book, that specifically teaches advanced and complex sql queries like this in goldmine?

 

[goner]

Well, you might be going about this the wrong way. Might I suggest you check out the Automated Processes area. It's build for just this sort of 'drip marketing' campaign...

Doug Castell
Castell Computers

http://www.castellcomputers.com

 

[ddeegan]

advanced and complex sql queries like this in goldmine?"

Here is one I use to retrieve contacts and e-mails when the contact has an indicator on the refrence field

select distinct
key1 , c1.dear as 'Salutation', c1.contact as 'Contact', c2.contsupref as 'E-mail Address'
from
contsupp as c1 inner join contsupp as c2 on c1.accountno = c2.accountno
join contact1 on contact1.accountno=c1.accountno
where c1.rectype = 'C' and (c2.rectype = 'P' and c2.contact = 'E-mail Address' and c2.linkacct =
c1.recid) and u_key1<>''
and c1.contsupref='Special Contact'
and right(rtrim(c2.contsupref),3) in ('com', 'edu', 'gov', 'int', 'mil', 'net', 'org', 'biz')
and c2.contsupref like '%@%'
order by 1

--The following SQL query will return the completed activities for all users.

select * FROM CONTHIST WHERE DAY(ONDATE) = day(getdate()) AND MONTH(ondate) = MONTH(GETDATE()) AND YEAR(ONDATE)=YEAR(GETDATE())


-- most active contact records

--(based on number of histories in the last month)
--Note, this works only on SQL-based GoldMine...

SELECT company, count(ondate)as Activities
FROM contact1,conthist
WHERE contact1.accountno = conthist.accountno
AND MONTH(ondate) = MONTH(getdate())-1
AND YEAR(ondate) = YEAR(getdate())
GROUP BY company,contact1.accountno
ORDER BY Activities DESC

Good Luck

 

[goner]

check out the FAQ's as well for more queries:

http://www.tek-tips.com/faq.cfm?pid=868

Doug Castell
Castell Computers

http://www.castellcomputers.com

 

[DunnDeal]

Thank you Doug for the FAQ. I did not know one existed for this site.

You have been a great help for this forum along with all of the other forums.

Thanks for all of your help,

Charlie Dunn

Charlie Dunn