Create Useful SQL Queries

If the GoldMine Administrator doesn’t take responsibility for helping the firm to deploy SQL queries this feature in GoldMine will probably not get done; Which would be a real shame because this is one of the most valuable features in GoldMine Corporate and Premium Editions!

I’ve written about SQL queries in previous issues of the GM-Success.com newsletter.  Here’s a link to a “Managing Smarter” article on SQL queries that appeared in GM-Success as well. In the last issue I even published a case study about a firm that transformed their business with GoldMine Premium and SQL queries (Read case).  

Because SQL Queries can search any of the tables in GoldMine (history, calendar, fields, opportunities, etc.) at once, you can get to information that is not available through the ‘out-of-the-box’ views GoldMine gives you otherwise. 

Here’s an example of an SQL query:

The query below returns completed activities in the past 30 days with an activity code of “DEM.”

Select conthist.ondate, conthist.userid, conthist.actvcode, contact1.company, contact1.contact, conthist.ref
from conthist
join Contact1 on contact1.accountno=conthist.accountno
where (conthist.ondate <= getdate() and conthist.ondate > getdate() – 30) and conthist.actvcode=’DEM’
order by conthist.ondate

The query below returns pending calls older than the today with an activity code of “SI.”

select convert(varchar(10), cal.OnDate, 101 )as Date, cal.userid, cal.ref,
contact1.Company, contact1.key1 as ‘Record Type’, contact1.key5 as ‘Classification’,
contact1.key1 as ‘Source’
from contact1
inner join cal on cal.accountno=contact1.accountno
where cal.actvcode=’SI’ and cal.rectype=’C’ and datediff(day, cal.ondate,
getdate()) > 0
Order by cal.ondate

With SQL queries you can select records you want and specify the information you want displayed.  There’s much more that’s possible with SQL queries – so it really calls for a combination of technical skills and your imagination to achieve what is most useful to your business.  

If your organization is not using SQL queries – why not?!  “We don’t need them” is not valid — there are just too many good reasons to be using SQL queries with GoldMine!  By the same token, if the answer is “we don’t understand how to implement them,” then please contact First Direct Corp. and let us give you a hand.  Email us at info@1stdirect.com or call (800)935-4386.