Adimpact.com

From the category archives:

SQL Query

Over the years, I’ve post many SQL Queries that GoldMine users have found very useful.  To see the entire list check out the SQL Category on the right or just use this link: http://www.thegoldmineblog.com/?cat=8

For any of those, here’s how to export those results.  Right-click in the results area, then choose Output To and Excel.  While you can output the results to Word or the Clipboard, you’ll find the formatting off… Excel works the best.  From there you can massage the data, print, save, email, etc.

GoldMine Output SQL Query

The only times you’ll run into trouble is if the SQL Query includes notes, those tend not to transfer well.

GoldMine’s Forecasted Sales allow you to keep track of your open sales pipeline.  If you’re not currently using Forecasted Sales check out this post for the basics: http://www.thegoldmineblog.com/?p=2017

Here’s a handy SQL Query to show all of the past due Forecasted Sales for the team.  To run the query go to Tools | SQL Query:

Past Due Forecasted Sales

Then copy and paste this in:

SELECT   ca.userid                 ,
         c1.company                ,
         c1.contact                ,
         ca.ondate                 ,
         ca.number1  AS Amt        ,
         ca.duration AS Probability,
         ca.ref                    ,
         c1.accountno
FROM     cal ca
         INNER JOIN contact1 c1
         ON       ca.accountno = c1.accountno
WHERE    rectype               = 'S'
AND      ondate               <= GETDATE()
ORDER BY userid

Happy New Year’s Everyone!

I’m looking forward to another great year with GoldMine, and if you have any suggestions for blog postings you’d like to see please drop me a line at chad.smith(@)thegoldmineblog.com!

GoldMine’s Opportunity Manager gives you a great place to track all of your open opportunities.  You can even record your calls and appointments against an opportunity, which are all tracked within the Opportunity Manager.

GoldMine Opportunity Manager History Tab

Of course, we can’t remember all the opportunities we have and open and should be following up on, so here’s a SQL Query to show you all of your open opportunities sorted by the last time you have a history record recorded for them.  To run the query go to Tools | SQL Query:

GoldMine Neglected Opportunity SQL Query

Use this SQL Query:

SELECT   ch.lasthistory,
         o.userid      ,
         o.company     ,
         o.contact     ,
         o.name        ,
         o.status      ,
         o.cycle       ,
         o.stage       ,
         o.source      ,
         o.startdate   ,
         o.foramt      ,
         o.forprob     ,
         o.accountno
FROM     opmgr o
         LEFT OUTER JOIN
                  (SELECT  MAX(ondate) AS LastHistory,
                           loprecid
                  FROM     conthist
                  GROUP BY loprecid
                  )
                  ch
         ON       o.recid = ch.loprecid
WHERE    rectype          = 'O'
ORDER BY ch.lasthistory

Just copy and paste it into the query window and hit Query

I think this concept would be very applicable to some GoldMine Dashboards as well.  If you’re looking for some dashboard help please drop me a line at chad.smith@thegoldmineblog.com.

Hey, Hope everyone is having a great summer!

Here’s a handy SQL Query to show everyone you’ve had a call or appointment with in the last 90 days.  This could be a great list to use to remind of you follow ups you could be doing.

Go to Tools | SQL Query in GoldMine, copy and paste this in and hit query.

Simply change the part where it says ‘USERID = ‘Bill’ to your userid in GoldMine so you’ll be seeing only your activities.

 

SELECT c1.contact   ,
       c1.company   ,
       c1.phone1    ,
       ch.ondate    ,
       ch.ref       ,
       ch.actvcode  ,
       ch.resultcode,
       c1.accountno
FROM   contact1 c1
       INNER JOIN conthist ch
       ON     c1.accountno = ch.accountno
WHERE  ch.srectype IN ('A',
                       'C')
AND    ch.ondate >= GETDATE() - 90
AND    userid     = 'BILL'

Here’s a handy SQL Query that’ll let you search your Notes tab.

Go to Tools | SQL Query, then paste this query into the box, then change ‘GoldMine’ to whatever you’d like to search for!

SELECT   c1.company   ,
 c1.contact   ,
 c1.key1      ,
 n.createddate,
 n.userid     ,
 n.note       ,
 c1.accountno
FROM     contact1 c1
 INNER JOIN notes n
 ON       c1.accountno = n.accountno
WHERE    n.rectype             = 'c1'
AND      CAST(CAST(n.note AS VARBINARY) AS VARCHAR(8000)) LIKE '%GoldMine%'
ORDER BY n.userid,
 createddate

For quick, easy, and cheap communication with your leads, prospects, and customers, having an email address is of utmost importance.  While your database is probably quite old, you can start monitoring new contacts that were created without an email address today.

Use this to encourage colleagues to get email addresses for these people, and even run a contest to see who can get the most email addresses!

So here’s a SQL query that will show you all your contacts added within the last 30 days that don’t have an email address.

SELECT   company,
 contact,
 city   ,
 state  ,
 zip    ,
 key1   ,
 key2   ,
 key3   ,
 key4   ,
 key5
FROM     contact1
WHERE    accountno NOT IN
 (SELECT accountno
 FROM    contsupp
 WHERE   contact = 'E-mail Address'
 AND     rectype = 'P'
 )
AND      createon >= GETDATE() - 30
ORDER BY company,
 contact

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

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

GoldMine Guide to SQL Queries

Happy New Year Everyone!

If one of your New Year’s resolutions is to improve your follow ups here’s a SQL Query that you’ll find helpful.  It will show a list of contacts as well as the date of the last completed appointment, and next scheduled appointment, as well as last completed call, and next scheduled call.

SELECT   c1.company            ,
 c1.contact            ,
 c1.phone1             ,
 la.ondate AS Last_Appt,
 na.ondate AS Next_Appt,
 lc.ondate AS Last_Call,
 nc.ondate AS Next_Call,
 c1.key1               ,
 c1.key2               ,
 c1.key3               ,
 c1.key4               ,
 c1.key5               ,
 c1.accountno
FROM     contact1 c1
 LEFT OUTER JOIN
 (SELECT  MAX(ch.ondate) AS ondate,
 ch.accountno
 FROM     conthist ch
 WHERE    srectype = 'A'
 GROUP BY accountno
 ) AS la
 ON       la.accountno = c1.accountno
 LEFT OUTER JOIN
 (SELECT  MAX(ca.ondate) AS ondate,
 ca.accountno
 FROM     cal ca
 WHERE    rectype = 'A'
 GROUP BY accountno
 ) AS na
 ON       na.accountno = c1.accountno
 LEFT OUTER JOIN
 (SELECT  MAX(ch.ondate) AS ondate,
 ch.accountno
 FROM     conthist ch
 WHERE    srectype = 'C'
 GROUP BY accountno
 ) AS lc
 ON       lc.accountno = c1.accountno
 LEFT OUTER JOIN
 (SELECT  MAX(ca.ondate) AS ondate,
 ca.accountno
 FROM     cal ca
 WHERE    rectype = 'C'
 GROUP BY accountno
 ) AS nc
 ON       nc.accountno = c1.accountno
WHERE    c1.key1               = 'Customer'
AND      c1.key4               = 'BILL'
ORDER BY c1.company

To run this simply go Tools | SQL Query then past in the query and click on Query.

You’ll notice at the end in the WHERE clause I have:

c1.key1 = ‘Customer’

which will limit the contacts returned to just customers.

I also have:

c1.key4 = ‘BILL’

which limits these only to Bill’s contacts.  You will want to tweak fields and values for your particular needs, or take the entire where clause out for the entire database.

If you’ve been using GoldMine for a while, and I am sure you have, you probably have a lot of old and outdated data in your database.  For example, everyone probably has something of an Account Type field in your system.

Our pick-list is quite current and update to date, but how do you see if there are any outdated entries in the system?

The solution is pretty easy!

First, find the database name of the field you want to review.  This can be done with a Right-Click | Properties of the field in question.

Since this is my KEY1 field I can use this SQL Query:

SELECT   key1     AS field,
 COUNT(*) AS COUNT
FROM     contact1
GROUP BY key1
ORDER BY COUNT(*) DESC

To run this simply go Tools | SQL Query then past in the query and click on Query.

If the field you want to examine does not have a ‘U’ in front of it simply swap out KEY1 for your field name in the query.  If the field does begin with a ‘U’ then swap out the field name as well as contact1 for contact2 as you’ll be looking at a user-defined field.

From here you can use filters to find those records that don’t fit and fix as needed.

Hey there – hope everyone is having a great summer!

Keeping your data clean in a CRM system like GoldMine, can be a chore.  Here’s a nifty SQL Query that’ll help you find invalid email addresses in Goldmine.  Simply Copy & Paste this into GoldMine’s SQL Query window (Tools | SQL Query) and start cleaning!

SELECT c1.company                                      ,
 c1.contact                                      ,
 cs.contsupref + ISNULL(cs.address1, '') AS email,
 c1.accountno
FROM   contact1 c1
 INNER JOIN contsupp cs
 ON     c1.accountno = cs.accountno
 AND    cs.contact   = 'E-mail Address'
 AND    cs.rectype   = 'P'
 AND
 (
 (
 cs.contsupref + ISNULL(cs.address1, '') NOT LIKE '%@%'
 )
 OR
 (
 cs.contsupref + ISNULL(cs.address1, '') LIKE '%]%'
 )
 OR
 (
 cs.contsupref + ISNULL(cs.address1, '') LIKE '%[%'
 )
 OR
 (
 cs.contsupref + ISNULL(cs.address1, '') LIKE '%)%'
 )
 OR
 (
 cs.contsupref + ISNULL(cs.address1, '') LIKE '%)%'
 )
 OR
 (
 cs.contsupref + ISNULL(cs.address1, '') LIKE '%,%'
 )
 OR
 (
 cs.contsupref + ISNULL(cs.address1, '') LIKE '%..%'
 )
 OR
 (
 cs.contsupref + ISNULL(cs.address1, '') LIKE '%/%'
 )
 OR
 (
 cs.contsupref + ISNULL(cs.address1, '') LIKE '%\%'
 )
 )

Don’t miss our June Special on GoldMineTraining.NET

I have updated “The Guide” for GoldMine 9.0.  Updates include sections on dealing with notes, the new notes table, as well as using the SQL Case/When statement.

GoldMine Guide to SQL Queries

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.

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                                            ,
 CONVERT(varchar(10), conthist.ondate, 101) AS activity_date,
 conthist.ref                                               ,
 CASE
 WHEN srectype = 'A'
 THEN 'Alert'
 WHEN srectype = 'L'
 THEN 'Letter'
 WHEN srectype = 'R'
 THEN 'Required Field'
 WHEN srectype = 'C'
 THEN 'Call'
 WHEN srectype = 'M'
 THEN 'Email'
 WHEN srectype = 'D'
 THEN 'To Do'
 WHEN srectype = 'O'
 THEN 'Other'
 WHEN srectype = 'T'
 THEN 'Action'
 WHEN srectype = 'S'
 THEN 'Sale'
 WHEN srectype = 'K'
 THEN 'Task'
 ELSE srectype
 END AS activity_type,
 contact1.company
FROM     contact1
 INNER JOIN conthist
 ON       contact1.accountno = conthist.accountno
 AND      conthist.ondate BETWEEN '1/1/2011' AND '1/31/2011'
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!

To change the date range simply change the dates after ‘Between’.

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.

GoldMine Guide to SQL Queries

Ensuring that newly created records have the appropriate information filled in such as company name, contact name, address, phone, etc. can be an arduous task. However, its importance is evident when you attempt to do marketing or follow ups.

Here’s a SQL Query that will show you any records created within the last 30 days that are missing one or more of the following: Company, contact, source, address1, city, state, zip or phone1. To change the date range simply change the last number in the query from 30 to however long you’d like to go back.

SELECT company ,
       contact ,
       source  ,
       address1,
       city    ,
       state   ,
       zip     ,
       phone1  ,
       key1    ,
       createby,
       createon
FROM   contact1
WHERE  (
              ISNULL(company, '')  = ''
       OR     ISNULL(contact, '')  = ''
       OR     ISNULL(source, '')   = ''
       OR     ISNULL(address1, '') = ''
       OR     ISNULL(city, '')     = ''
       OR     ISNULL(zip, '')      = ''
       OR     ISNULL(phone1, '')   = ''
       OR     ISNULL(key1, '')     = ''
       )
AND    createon > GETDATE() - 30

While not a particularly fun task, ensuring your GoldMine database is properly populated is an important job.

In GoldMine, a contact may have multiple email addresses.  This is useful when, for example, you might want to track someone’s business email address as well as a personal email address.  When you click in the email area from the main contact, a window will pop up with all the email addresses associated with the primary and secondary contacts.

goldmine-email-1

However, there may be a contact that should have a primary email addresses, but doesn’t.  This can cause problems when doing email blasts, or having a random email address displayed in the top half of the screen.  One of the nice new features in GoldMine 9.0 is that if a contact has an email addresses, but doesn’t have any marked as primary you’ll know with the “Click to Select” message.
goldmine-email-2
However, to examine your entire database for contacts with email addresses, but none marked as primary, here’s your SQL Query:

SELECT   company,
         contact,
         phone1 ,
         accountno
FROM     contact1
WHERE    accountno IN
         (SELECT accountno
         FROM    contsupp
         WHERE   contact = 'E-mail
Address'
         )
AND      accountno NOT IN
         (SELECT accountno
         FROM    contsupp
         WHERE   contact            = 'E-mail Address'
         AND     substring(zip,2,1) = '1'
         )
ORDER BY company

To use, go to Tools | SQL Query (Or Look up | SQL Query in GoldMine Corporate/Standard Editions) and paste in then click Query.

goldmine-email-3

Notify

Here’s a SQL query that will list out all of your contacts with their last call, appointment, and sale.  Special thanks to Jill from W-Systems for this query!

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

SELECT accountno,lastname,contact,company,state,(select top 1 ondate
from conthist where contact1.accountno = conthist.accountno and
srectype='C'order by ondate desc) as Call,(select top 1 ondate from
conthist where contact1.accountno = conthist.accountno and srectype='A'order by
ondate desc) as Appointment,(select top 1 ondate from conthist where
contact1.accountno = conthist.accountno and srectype='S' order by ondate desc)
as Sale FROM contact1 ORDER by company ASC

last-contact-info

Here’s a SQL query that will list out all of your forecasted sales by age, or how long ago they should have closed.

GoldMine’s forecasted sales give you a quick and easy way of tracking possible sales so you better manage your pipeline and remind you of sales that should be closing.

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.

This SQL Query will work for SQL based GoldMine systems (GoldMine Corporate or Premium)…

select cal.userid, contact1.company, cal.ondate CloseDate,
datediff("d", ondate, getdate()) Age, cal.ref 'Product/Service',
cal.number1 value, cal.duration probability from cal, contact1
where rectype = 'S' and contact1.accountno = cal.accountno and
userid = 'BILL' order by ondate

Change the last part, “userid = ‘BILL’” and replace bill with your GoldMine userid or leave it out all together to see all forecasted sales for all users.

goldmine-forecasted-sale-sql-query

If you want to learn more about how to forecast sales check out GoldMineTraining.NET for a video lesson on the subject.

GoldMine Blog Notification Form

Finding and eliminating duplicate records is an important part of maintaining a clean contact database in GoldMine.  We’ve covered merging tagged records here:

http://www.thegoldmineblog.com/2009/03/time-for-some-goldmine-spring-cleaning-with-mergepurge/

After a merge/purge GoldMine will put the non-surviving record in the Additional Contacts tab.

goldmine-85-contact

If you are diligent, you will delete that right after the merge/purge, but of course we’re not all quite that perfect!

So here’s a SQL Query to find those so you can remove them.

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 contact1.company, contact1.contact, contact1.phone1 from
contact1, contsupp
where contact1.contact = contsupp.contact
and contact1.accountno = contsupp.accountno order by contact1.contact

goldmine-sql-query

{ 1 comment }

7-9-2009-9-11-51-pmHope 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 “)”.

sql-query-for-goldmine

5-26-2009-7-13-11-pmHere’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'

GoldMine Guide to SQL Queries

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

GoldMine History Screen Shot

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.

GoldMine Blog Notification Form

Here’s a SQL Query request that I came upon recently…

“I need to see all contacts I had an appointment with, completed appointments, as well as all contacts I have a scheduled appointment with, pending, for a particular date range.”

I could see this being useful for cleaning up activities scheduled, and done, but not completed, as well as for  contacts you may want to do some follow up with.  Managers wanting to monitor activity will also find this very useful!

So here’s the SQL Query:

select company, contact, phone1, address1, city, state, zip from contact1 where accountno in (select accountno from conthist where ondate >= '12/1/2008' and ondate <= '12/31/2008' and srectype = 'A') or accountno in (select accountno from cal where ondate >= '12/1/2008' and ondate <= '12/31/2008' and rectype = 'A') order by company

You can change the date range to whatever makes sense for you.

Also, you may want to add in an option so it just shows your activities…  Change the portion in the two sections where it says “and userid = ‘BILL’” to your userid.

select company, contact, phone1, address1, city, state, zip from contact1 where accountno in (select accountno from conthist where ondate >= '12/1/2008' and ondate <= '12/31/2008' and srectype = 'A' and userid = 'BILL') or accountno in (select accountno from cal where ondate >= '12/1/2008' and ondate <= '12/31/2008' and rectype = 'A' and userid = 'BILL') 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.  If you are using GoldMine Premium Edition then Tools | Filters & Groups and then the SQL Query tab.

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


Pick Up Your Copy Of The GoldMine Guide to SQL Queries Today!

GoldMine SQL
GoldMine Blog Notification Form