Adimpact.com

From the category archives:

SQL Query

I received this question from a long time blog reader, “Do you have a SQL Query that can list the Companies where there has been NO CHANGE, no pending record entered or History Record in the last year?” Well, no I don’t, asics gel pas cher but let’s see what we can do! So here’s the query:

SELECT c1.company, c1.contact,  Nike Free Run 3.0 femme c1.phone1, c1.address1,  asics gel lyte 3 c1.city,  louboutin chaussures c1.state,  Parajumpers Femme Californian Joanna c1.zip, c1.key1, c1.accountno FROM contact1 c1 WHERE lastdate < getdate() - 365 AND c1.accountno NOT IN (SELECT accountno FROM cal WHERE createon >= getdate() - 365) AND c1.accountno NOT IN (SELECT accountno FROM conthist WHERE createon >= getdate() - 365) AND c1.key1 <> 'Unqualified' ORDER BY c1.company, c1.contact 

Ignored Accounts SQL So a couple of things to note. Air Jordan 1 bambini You’ll notice I’m using getdate() – 365 to automatically go back one year, you could replace this with a date, Adidas Zx 500 Homme e.g. < 1/1/2015, adidas hamburg homme if you want to hard code the dates. Also, I am excluding any contact marked Unqualified in the key1 field for those contacts that you do not want to contact often.

Anytime you’re using a system like GoldMine, it’s a good idea to evaluate how often a field is populated and the different values that are being put in. asics noir soldes For example, here we have our Industry field. Nike Air Max 90 Femme Industry Are people using it? If so how many records do we have for the different industries? Fortunately this is an easy question to answer with a pretty simple SQL Query. chaussures timberland soldes First, however, you’ll need to do a Right-Click | Properties on the field to get the field name. This is how it’s referenced in the database. New Balance 574 femme Industry Properties So our SQL Query to analyze this will be:

SELECT key3, count(*) as '#' FROM contact1 GROUP BY key3

Simply copy and past the SQL Query into Tools | SQL Query and hit query. adidas stan smith homme bleu You’ll see a result like this. adidas gazelle femme You’ll notice there’s two blank areas, one for blanks and one for nulls, but essentially they’re the same thing. New Balance 1600 homme Query You can switch out the fields just be sure to change the name after the select as well as after the group by. chaussures louboutin You can also use this for other tables, for example contact2, for custom fields, just change the table name after the FROM.

Over the years, Parajumpers Masterpiece Passport 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, Nike Cortez femme here’s how to export those results. Nike Roshe Run Femme Right-click in the results area, New Balance 533 homme then choose Output To and Excel. While you can output the results to Word or the Clipboard, new balance femme you’ll find the formatting off… asics gel lyte v homme Excel works the best. Nike Blazer femme From there you can massage the data, Adidas Zx Flux Homme print, save, email, Asics Gel Lyte 3 Homme etc. GoldMine Output SQL Query The only times you’ll run into trouble is if the SQL Query includes notes, bottes ugg those tend not to transfer well.

GoldMine’s Forecasted Sales allow you to keep track of your open sales pipeline. Adidas Zx Flux Femme Porte 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. Adidas Zx 700 Homme To run the query go to Tools | SQL Query: Past Due Forecasted Sales Then copy and paste this in:

SELECT   ca.userid                 ,           Nike Flyknit Lunar femme c1.company                ,           nike air huarache uomo c1.contact                ,           Asics Gel Lyte 3 Femme ca.ondate                 ,           louboutin homme ca.number1  AS Amt        ,           nike air max 1 ultra moire uomo ca.duration AS Probability,           Nike Flyknit Lunar 1 femme ca.ref                    ,          

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, asics gel lyte v we can’t remember all the opportunities we have and open and should be following up on, Nike Free 5.0 enfants 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,           Asics Gel Lyte 3 Femme Blanche o.userid      ,          o.company     ,           Adidas Zx 500 Homme o.contact     ,           nike air jordan 31 uomo o.name        ,          o.status      ,           Nike Roshe Run Homme o.cycle       ,           New Balance France o.stage       ,          o.source      ,          o.startdate   ,          o.foramt      ,          o.forprob     ,           Canada Goose Constable Parka o.accountno FROM     opmgr o          LEFT OUTER JOIN                   (SELECT  MAX(ondate) AS LastHistory,                             Nike Air Max 2017 Homme 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. Air Jordan 2 Homme If you’re looking for some dashboard help please drop me a line at chad.smith@thegoldmineblog.com.

Here’s a handy SQL Query that’ll let you search your Notes tab. Asics Gel Lyte 5 Femme Rose Go to Tools | SQL Query, Nike Flyknit Lunar 4 femme then paste this query into the box, asics gel lyte 5 then change ‘GoldMine’ to whatever you’d like to search for!

SELECT   c1.company   ,  Doudoune Parajumpers Harraseeket c1.contact   ,  Nike Air Max BW Homme c1.key1      ,  Canada Goose n.createddate,  New Balance 1600 homme n.userid     ,  Nike Air Max 98 Femme n.note       ,  Nike Air Max 2016 enfants 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, 

For quick, easy, and cheap communication with your leads, nike free 4.0 uomo prospects, and customers, Air Jordan 3.5 Spizike Femme having an email address is of utmost importance. While your database is probably quite old, Nike Free Run 3.0 homme 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, nike air zoom pegasus 33 uomo 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   ,  new balance grise state  ,  timberland femme zip    , key1   , key2   , key3   ,  Asics Gel Lyte 3 Homme 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. New Balance 1600 homme Within the results, you can do a Right-Click | Output To | Excel to save the results, adidas bounce homme print them,

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. new balance homme This could be a great list to use to remind of you follow ups you could be doing. nike free 5.0 uomo grigio Go to Tools | SQL Query in GoldMine, adidas superstar 2 femme 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   ,         Adidas Zx 700 Femme Noir c1.company   ,         chaussures timberland pas cher c1.phone1    ,         timberland femme ch.ondate    ,        ch.ref       ,         Adidas Zx Flux Homme Noir ch.actvcode  ,         adidas hamburg homme ch.resultcode,        c1.accountno FROM   contact1 c1        INNER JOIN conthist ch        ON     c1.accountno = ch.accountno WHERE  ch.srectype IN ('A',                        

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. Adidas Zx Flux Femme Blanche 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, louboutin paris and next scheduled call.

SELECT   c1.company            ,  Air Jordan 8 Homme c1.contact            , c1.phone1             , la.ondate AS Last_Appt,  new balance 574 na.ondate AS Next_Appt, lc.ondate AS Last_Call,  asics gel nimbus uomo nc.ondate AS Next_Call, c1.key1               , c1.key2               ,  nike scarpe italia shop c1.key3               , c1.key4               ,  louboutin homme 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,  Nike Air Max 90 Femme Blanche 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. nike air force 1 enfants 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, Asics Gel Lyte 3 Femme Blanche and I am sure you have, Adidas Zx Flux Homme Grise 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. chaussures new balance Our pick-list is quite current and update to date, Nike Air Max BW Homme but how do you see if there are any outdated entries in the system? The solution is pretty easy! First, Adidas Superstar Femme Rose find the database name of the field you want to review. Adidas Zx Flux Femme Porte 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,  asics soldes 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. Nike Femme 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. Nike Air Huarache Homme From here you can use filters to find those records that don’t fit and fix as needed.

I have updated “The Guide” for GoldMine 9.0. New Balance 009 femme Updates include sections on dealing with notes, Nike Air Max 2016 Homme the new notes table, bottes ugg australia as well as using the SQL Case/When statement.

You’re not alone. ugg femme 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. Nike Air Max 90 enfants 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. nike air max flyknit homme So, to answer this question… Go to Lookup | SQL Queries in GoldMine. Adidas Zx Flux Homme 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. Air Jordan 2 Homme You’ll see your results. Adidas Ultra Boost Homme 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’. asics gel kayano This is a great example of how useful some simple SQL Queries can be.