SQL Query

Hope everyone is having a great summer!

Here’s a handy SQL query that might be a introduction to the ‘in’ statement for many of you.  This example will quickly find all the contacts in several different states without stringing together multiple ‘or’ statements.

select company, contact, source, key1, STATE, createon from contact1
where state in ('MA', 'NH', 'VT', 'CT', 'ME', 'RI')
order by state, company

To use this, from GoldMine’s main menu choose Lookup | SQL Queries then copy & paste the above in the top section then click Query.  If you are using GoldMine Premium Edition then Tools | Filters & Groups and then the SQL Query tab.

Changing which states will be list is as easy as changing what’s between the “(” and “)”.

Here’s a handy SQL Query to find all contacts created this month:

To use this, from GoldMine’s main menu choose Lookup | SQL Queries then Copy & Paste the above in the top section then click Query.  If you are using GoldMine Premium Edition then Tools | Filters & Groups and then the SQL Query tab.

select company, contact, createon, phone1, source, key1
from contact1
where year(createon) = year(getdate()) and
month(createon) = month(getdate())

Great to ensure the cleanliness of the data entered this month.

This will only work with SQL based systems since we’re using the getdate() function to determine the current date. If you’re using dBASE you’ll need to change the dates every time you use it.

select company, contact, createon, phone1, source, key1
from contact1
where createon >= '5/1/2009' and createon <= '5/30/2009'

If you are diligent about recording what you spoke with your contacts about in GoldMine…

It’s very easy to find everyone you spoke about a topic with.

Let’s say I want to find everyone where I mention ‘gadgets’.

Here’s the SQL Query:

select contact1.contact, contact1.company, conthist.ondate,
conthist.ref from contact1, conthist where contact1.accountno =
conthist.accountno and conthist.userid = 'BILL' and conthist.notes like
'%Gadgets%' order by ondate

Simply change the ‘BILL’ to your userid and replace ‘Gadgets’ with your search term!

To use this, from GoldMine’s main menu choose Lookup | SQL Queries then Copy & Paste the above in the top section then click Query.  If you are using GoldMine Premium Edition then Tools | Filters & Groups and then the SQL Query tab.

Ever wonder how many primary contacts, secondary contacts, or history items you have in your GoldMine database?

Here are three simple SQL Queries to give you an answer!

First, go to Lookup | SQL Queries or Go To | Filters & Groups | SQL Queries Tab in GoldMine Premium Edition.  Copy and paste the queries into the top half of the screen then click the Query button.

 
For a count of primary contacts:

Select count(*) from contact1  

For a count of secondary contacts:

Select count(*) from contsupp where rectype = 'C'

For a count of history records:

Select count(*) from conthist

You’re not alone.  You contemplate life and the weekend on a Friday afternoon and wonder what you, your sales reps, and/or your customer service personnel did the past week.  Fortunately, if you’ve trained your staff to actively use GoldMine to schedule and complete activities to the History Tab this is going to be a very easy question to answer.  

… if you haven't drop me a line (248.506.5222) to discuss some GoldMine training.. 

I actually run this every week for myself, usually during the laid back hour of 7:00 to 8:00 AM on a Saturday to see if I need to schedule some follow up activities for the following week.  It’s not that I particularly like being up at that time, but “Black Jack” my Cairn Terrier does!  Who’s to argue?

So, to answer this question…  Go to Lookup | SQL Queries in GoldMine.  Paste in this code to the upper part of the screen:

select conthist.userid, conthist.ondate, conthist.ref, conthist.rectype, contact1.company
from contact1, conthist
where contact1.accountno = conthist.accountno and conthist.ondate >= '6/8/2007' and conthist.ondate <= '6/16/2007'
order by userid, ondate

Then click Query.

You’ll see your results.  If you find this useful use the ‘Save’ button to reuse this later on and just change the date range…  Within the results, you can do a Right-Click | Output To | Excel to save the results, print them, and further analyze!

If  you are on GoldMine Corporate Edition or GoldMine Premium Edition you can use this query:

select conthist.userid, conthist.ondate, conthist.ref, conthist.rectype, contact1.company
from contact1, conthist
where contact1.accountno = conthist.accountno and conthist.ondate >= getdate() - 7 order by userid, ondate

To automatically show you the past 7 days.

This is a great example of how useful some simple SQL Queries can be.  To learn more about queries check out The GoldMine Guide to SQL Queries.

… not a pleasant thought, eh?

Fortunately, though, if you use GoldMine forgetting about, and then loosing, an important customer because you ‘forgot’ about them is easy to avoid. 

Here’s a SQL Query that will show us all clients that have no history this year:

select contact1.company, contact1.contact, contact1.phone1, contact2.lastconton
from contact1, contact2
where contact1.accountno = contact2.accountno and
contact1.accountno not in (select accountno from conthist where ondate >= '1/1/2007') and
contact1.key1 = 'Client' and
contact1.key4 = 'Chad'
order by company

To use this, from GoldMine’s main menu choose Lookup | SQL Queries then Copy & Paste the above in the top section then click Query. 

There are a couple of tweaks that you may want to make so this work optimally on your system.

First, the contact1.key1 = ‘Client’.  My GoldMine is configured so the Key1 field, normally located at the top of the lower-right quadrant, denotes client, prospect, etc.  You may want to change that to the field you use and/or the correct nomenclature, e.g. client, customer, etc.

(Here's a screen shot so you know what I mean by lower-right quadrant).

Second, the contact1.key4 = ‘Chad’.  My GoldMine is configured so the Key4 field, normally located in the 4^th position in the lower-right quadrant, denotes the account manager.  You may want to change that to the field you use and/or correct nomenclature.

Also, you may want to change the date, you can use any date in there that makes sense.

For more information on saving queries, using queries to make groups, etc. check out The GoldMine Guide to SQL Queries at http://www.thegmblog.com/sql4gm

Ever wondered where your records are coming from? 

Here’s a SQL Query that’ll show you the number of records created, by their source for 2007.  The dates can easily be changed so show whatever date range you’d like.  This should work on any SQL based GoldMine system. 

select source, count(*) as "#", max(accountno)
from contact1
where createon >= ‘1/1/2007′ and createon <= ‘12/31/2007′
group by source
order by source

If you’re using a dBASE GoldMine, this won’t give a summary but will display the information:

select source, company
from contact1
where createon >= ‘1/1/2007′ and createon <= ‘12/31/2007′
order by source

P.S. Want to learn how to write your own SQL Queries in GoldMine?
Check out http://www.thegmblog.com/sql4gm/

{ 2 comments }

Here’s a SQL Query request that comes up often…  How to find all secondary contact and their email addresses:

Here ya’ go!

select contact1.company, contsupp.contact, contsupp2.contsupref + contsupp2.address1 from contact1, contsupp, contsupp as contsupp2 where contsupp.rectype = ‘C’ and contact1.accountno = contsupp.accountno and contsupp.recid = contsupp2.linkacct order by contact1.company

Now wouldn’t the GoldMine Guide To SQL Queries (http://www.thegmblog.com/sql4gm/) make a great Holiday gift for that special GoldMiner in your life?

{ 2 comments }

Here’s a handy SQL Query for you.  It will show all primary and secondary contacts in the database.  If the secondary contact has an address it’ll display that address and if they don’t it’ll use the address information from the primary contact.  To use go to:

Lookup | SQL Query then paste this into the box and hit query.  I tested this on 6.7 SQL 2000 & 7.0 SQL 2005.  It won’t work on dBASE.

select company, contact, title, address1, address2, address3, city, state, zip, phone1, contact1.accountno from contact1
union
select contact1.company, contsupp.contact, contsupp.title, contact1.address1, contact1.address2, contact1.address3, contact1.city, contact1.state, contact1.zip, contsupp.phone, contact1.accountno from contact1, contsupp where rectype = ‘C’ and (contsupp.address1 is null or contsupp.address1 <= ”) and contact1.accountno = contsupp.accountno
union
select contact1.company, contsupp.contact, contsupp.title, contsupp.address1, contsupp.address2, contsupp.address3, contsupp.city, contsupp.state, contsupp.zip, contsupp.phone, contact1.accountno from contact1, contsupp where rectype = ‘C’ and contsupp.address1 > ‘ ‘ and contact1.accountno = contsupp.accountno
order by company, contact

{ 1 comment }