SQL Learning Center - GoldMine

Use SQL queries to get answers from GoldMine

May 30, 2008

Counts of Contact Type (key1) for a Specific List of Contact Types

by Doug Steinschneider @ 7:44 am. Filed under MSSQL Counts

Type: MSSQL
Description: This query will give you counts for a list of Contact Types you give it in the WHERE portion

  1.  
  2. SELECT
  3.  COUNT(c1.accountno) AS Investor_Count
  4. ,c1.key4 AS AcctMgr
  5. ,c1.key1
  6. FROM
  7.  contact1 c1 (NOLOCK)
  8. WHERE
  9.  c1.accountno IN (
  10.  SELECT
  11.   c1.accountno
  12.  FROM
  13.   contact1 c1
  14.    JOIN conthist ch
  15.    ON c1.accountno=ch.accountno
  16.  WHERE
  17.   ch.userid = c1.key4
  18.   AND c1.key1 IN (
  19.   'Broker'
  20.  ,'Charity'
  21.  ,'Consultant'
  22.  ,'Corporate Pension'
  23.  ,'Endowment'
  24.  ,'Family Office'
  25.  ,'Foundation'
  26.  ,'High Net Worth'
  27.  ,'Insurance Company'
  28.  ,'Investment Advisor'
  29.  ,'Investment Manager'
  30.  ,'Investor'
  31.  ,'Law Firm'
  32.  ,'Manager'
  33.  ,'Other'
  34.  ,'Pension'
  35.  ,'Prime Broker'
  36.  ,'Private Bank'
  37.  ,'Private Equity'
  38.  ,'Public Pension'
  39.  ,'Trust'
  40.  ,'Union Pension'
  41.  ,'University'
  42.  ,'Wealth Management'
  43.  ,''))
  44. GROUP BY
  45.  c1.key4
  46. ,c1.key1
  47.  

May 28, 2008

Find Deleted Contacts by Comparing Current Database with Older Version

by Doug Steinschneider @ 9:53 pm. Filed under MSSQL Query Analyzer, MSSQL Contacts

Type: MSSQL
Description: In this more advanced query we see the following scenario: You suspect that some contacts have been deleted from GoldMine when instead they should have been moved to your Archive Database. We move contacts flagged for deletion to a database called GoldMine_Archive. In this query we check for GoldMine accountnos in an older backup that weve restored to an alternate MSSQL database GoldMine_20070806 against all the accountnos in both the current GoldMine and GoldMine_Archive.

  1.  
  2. SELECT
  3.  c1r.contact
  4. ,c1r.company
  5. ,c1r.city
  6. ,c1r.STATE
  7. ,c1r.key1
  8. ,c1r.key2
  9. ,c1r.key3
  10. ,c1r.key4
  11. ,c1r.key5
  12. ,c1r.accountno
  13. FROM
  14.  GoldMine_20070806.dbo.CONTACT1 c1r
  15. WHERE
  16.  c1r.accountno NOT IN
  17.  (SELECT accountno FROM contact1 UNION SELECT accountno FROM GoldMine_Archive.dbo.CONTACT1)
  18. ORDER BY
  19.  c1r.company
  20. ,c1r.contact
  21.  

by Doug Steinschneider @ 9:39 pm. Filed under MSSQL Email

Type: MSSQL
Description: This query returns email records for a contact that have keywords in the body of the email. In the example below we are looking for emails that contain the words “Subscription”, “Offering”, “Document ” or “Documents “. Notice the trailing space after the document and documents examples.

  1.  
  2. SELECT
  3.  c1.contact
  4. ,c1.company
  5. ,mb.maildate
  6. ,mb.mailref
  7. ,c1.accountno
  8. FROM
  9.  contact1 c1
  10.   JOIN conthist ch (NOLOCK)
  11.    ON c1.accountno=ch.accountno
  12.     JOIN mailbox mb
  13.      ON ch.recid = mb.linkrecid
  14. WHERE
  15.  mb.rfc822 LIKE '%type keywords between percent signs like examples below%'
  16.  AND (mb.rfc822 LIKE '%Subscription%'
  17.   OR mb.rfc822 LIKE '%Offering%')
  18.  AND (mb.rfc822 LIKE '%Document %'
  19.   OR mb.rfc822 LIKE '%Documents %')
  20.  AND mb.folder = 'Filed'
  21. GROUP BY
  22.  c1.contact
  23. ,c1.company
  24. ,mb.maildate
  25. ,mb.mailref
  26. ,c1.accountno
  27.  

Search for History Logged Field Updates

by Doug Steinschneider @ 11:18 am. Filed under MSSQL History

Type: MSSQL
Description: In this query we are searching for history records that were logged by GoldMine (because create a history log record for updates is checked in the field setup). In this particular case we are looking for key4 when it was changed from ‘DCS’ to ‘KES’ after June 1st 2006.
Note: This query needs to be rewritten using PATINDEX to account for varying start postions. In this case it works because all Rep IDs are 3 character.

  1.  
  2. SELECT
  3.  c1.contact
  4. ,c1.company
  5. ,c1.city
  6. ,c1.STATE
  7. ,c1.key4
  8. ,ch.REF
  9. ,ch.createon
  10. ,c1.accountno
  11. FROM
  12.  contact1 c1 (NOLOCK)
  13.  JOIN conthist ch
  14.  ON c1.accountno=ch.accountno
  15. WHERE
  16.  ch.createon > '06/01/2006'
  17.  AND ch.resultcode= 'LOG'
  18.  AND SUBSTRING(ch.REF,28,3) = 'DCS'
  19.  AND SUBSTRING(ch.REF,37,3) = 'KES'

May 27, 2008

Counts of Call Histories by UserID

by Doug Steinschneider @ 3:10 pm. Filed under MSSQL Counts, MSSQL History

Type: MS SQL Query
Description: This gives you a count by user for the calls they created a history for. In this case I limited the date range to the year 2007.

  1.  
  2. SELECT
  3.  ch.userid
  4. ,COUNT(*) AS Call_Count
  5. FROM conthist ch
  6. WHERE
  7.  ch.rectype LIKE 'C%'
  8.  AND YEAR(ch.ondate) = '2007'
  9. GROUP BY ch.userid
  10.  

Counts of Call Histories by User for Year

May 26, 2008

All Activity in Past 30 Days

by Doug Steinschneider @ 10:43 pm. Filed under MSSQL History

Description: This query returns all histories added to GoldMine in the past 30 days.

  1.  
  2. SELECT
  3.  ch.userid
  4. ,ch.ondate
  5. ,ch.rectype
  6. ,c1.company
  7. ,c1.contact
  8. ,ch.REF
  9. ,ch.actvcode
  10. ,ch.resultcode
  11. ,ch.accountno
  12. FROM
  13.  conthist ch (NOLOCK)
  14.  JOIN contact1 c1
  15.   ON ch.accountno=c1.accountno
  16. WHERE
  17.  ch.ondate > GETDATE()-30
  18.  

Book Review - The Practical SQL Handbook

by Doug Steinschneider @ 10:19 pm. Filed under Books on SQL, Intermediate

What I liked about Judith Bowman’s “The Practical SQL Handbook” is that it’s easy to read and the examples aren’t difficult to try yourself. It starts with an explanation of how the SQL language was developed and why the developers designed it as they did. It’s not specific to any single SQL implementation but intead covers aspects of the Query Language that are common to all SQL implementations. If you are completely new to SQL you should try one of the books categorized here as Beginner but this book should be your second SQL read.

Guru’s Guide to Transact-SQL - Ken Henderson

by Doug Steinschneider @ 10:44 am. Filed under Books on SQL, Expert

This is the book I’ve turned to when I’m up against a very challenging request for either a query or a script. It’s written in a very straightforward manner. Every code example, there are over 600, is relevant to what Ken is explaining. There is no fluff in this book whatsoever.

May 25, 2008

Contact Email with Specific Mergecode for Primary and Additional Contacts

by Doug Steinschneider @ 10:43 am. Filed under MSSQL Email, MSSQL Contacts

Categories: Contact, Email

Description: This returns all emails for a mergecode specified in the WHERE portion of the query between the percent signs for both primary and additional contact email addresses.

  1.  
  2. SELECT
  3.  c1.company
  4. ,cs.contact
  5. ,c1.address1
  6. ,c1.address2
  7. ,c1.city
  8. ,c1.STATE
  9. ,c1.zip
  10. ,c1.country
  11. ,cs1.contsupref AS Email
  12. ,cs1.mergecodes
  13. ,c1.accountno
  14. FROM
  15.  contsupp cs (NOLOCK)
  16.   LEFT OUTER JOIN contsupp cs1
  17.    ON cs.recid = cs1.linkacct
  18.     LEFT OUTER JOIN contact1 c1
  19.      ON cs.accountno = c1.accountno
  20. WHERE
  21.  cs.rectype = 'C'
  22.  AND cs1.rectype = 'P'
  23.  AND cs1.contact = 'E-mail Address'
  24.  AND cs1.mergecodes LIKE '%Put mergecode here between percent signs%'
  25. UNION
  26. SELECT
  27.  c1.company
  28. ,c1.contact
  29. ,c1.address1
  30. ,c1.address2
  31. ,c1.city
  32. ,c1.STATE
  33. ,c1.zip
  34. ,c1.country
  35. ,cs.contsupref AS AddContEmail
  36. ,cs.mergecodes AS AddlMergeCode
  37. ,c1.accountno
  38. FROM
  39.  contact1 c1 (NOLOCK)
  40.   JOIN contsupp cs
  41.    ON c1.accountno=cs.accountno
  42. WHERE
  43.  cs.mergecodes LIKE '%Put mergecode here between percent signs%'
  44.  AND cs.rectype = 'P'
  45.  AND cs.contact = 'E-mail Address'
  46.  AND cs.zip LIKE '_1__'
  47.  

Contact Email with Specific Mergecode for Primary Contacts Only

by Doug Steinschneider @ 9:46 am. Filed under MSSQL Email


Type: MS SQL
Categories: Contact, Email

Description: This returns contact information along with the primary email address for those contacts that have a value in the email mergecode which is specified in the WHERE portion of the query at cs.mergecodes LIKE ‘%Put mergecode between these percent signs%’. The % sign allows you to search of that value anywhere in the mergecode field.

  1.  
  2. SELECT
  3.  c1.company
  4. ,c1.contact
  5. ,c1.address1
  6. ,c1.address2
  7. ,c1.city
  8. ,c1.STATE
  9. ,c1.zip
  10. ,c1.country
  11. ,c1.phone1
  12. ,cs.contsupref AS EmailAddress
  13. ,cs.mergecodes
  14. ,c1.key4 AS Rep
  15. ,c1.accountno
  16. FROM
  17.  contact1 c1 (NOLOCK)
  18.   JOIN contsupp cs
  19.    ON c1.accountno=cs.accountno
  20. WHERE
  21.  cs.mergecodes LIKE '%Put MergeCode value between these percent signs%'
  22.  AND cs.rectype = 'P'
  23.  AND cs.contact = 'E-mail Address'
  24.  AND cs.zip LIKE '_1__'
  25.        

GoldMine SQL Tutorial:

General SQL Tutorials:

SQL Help Links:

Recommended Reading - SQL Books:

RSS:

Links:

Search For SQL Query:

GoldMine MS SQL Queries:

GoldMine dBase SQL Queries (LocalSQL):

« Previous Entries

21 queries. 0.177 seconds