SQL Learning Center - GoldMine

Use SQL queries to get answers from GoldMine

September 11, 2008

Find all contacts sent a certain attachment

by Doug Steinschneider @ 9:11 pm. Filed under MSSQL History, MSSQL Email

Type: MSSQL
Description: Find all contacts sent a certain attachment

  1.  
  2. SELECT
  3.  c1.contact
  4. ,c1.company
  5. ,ch.rectype
  6. ,mb.maildate
  7. ,c1.accountno
  8. FROM
  9.  contact1 c1
  10.  JOIN conthist ch
  11.  ON c1.accountno=ch.accountno
  12.  JOIN mailbox mb
  13.   ON ch.recid = mb.linkrecid
  14. WHERE
  15.  mb.maildate > '11/01/2007'
  16.  AND mb.rfc822 LIKE '%Put attachment filename here%'
  17.  
Bookmark:

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • del.icio.us
  • Live
  • Google
  • Facebook
  • bodytext
  • StumbleUpon
  • Slashdot

August 13, 2008

Find Main record for an additional contact

by Doug Steinschneider @ 7:46 pm. Filed under MSSQL Contacts

Type: MSSQL
Description: Find Main record for an additional contact

  1.  
  2. SELECT
  3.  c1.accountno
  4. ,c1.company
  5. ,c1.contact
  6. ,c1.key5
  7. FROM
  8.  contact1 c1
  9.  JOIN contsupp cs
  10.   ON c1.accountno=cs.accountno
  11. WHERE
  12.  cs.contact LIKE '%John Smith%'
  13.        
Bookmark:

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • del.icio.us
  • Live
  • Google
  • Facebook
  • bodytext
  • StumbleUpon
  • Slashdot

History Orphans

by Doug Steinschneider @ 7:40 pm. Filed under MSSQL History

Type: MSSQL
Description:Find history records that have no parent contact record

  1.  
  2. SELECT
  3.  *
  4. FROM
  5.  conthist ch
  6. WHERE
  7.  ch.userid='put user id in here'
  8.  AND ch.accountno NOT IN
  9.  (SELECT accountno FROM contact1)
  10.  
Bookmark:

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • del.icio.us
  • Live
  • Google
  • Facebook
  • bodytext
  • StumbleUpon
  • Slashdot

June 6, 2008

Contacts in 1st GoldMine Group But Not in 2nd GoldMine Group

by Doug Steinschneider @ 4:11 pm. Filed under MSSQL Groups

Type: MSSQL
Description: This query returns the accountnos (which can then be used to create a third group) of contacts who are in a 1st GoldMine Group but not in a 2nd GoldMine Group.

  1.  
  2.        
  3. SELECT
  4.  c1.accountno
  5. FROM
  6.  (SELECT
  7.    c1.accountno
  8.   FROM
  9.    contact1 c1
  10.   JOIN contgrps cg
  11.   ON c1.accountno=cg.accountno
  12.    JOIN contgrps cg1
  13.    ON cg.userid = cg1.recid
  14.   WHERE
  15.    LEFT(cg1.Accountno,2) = '*M'
  16.    AND (     cg1.REF = '1st GoldMine Group Name')) InGroup
  17.    WHERE  InGroup.accountno
  18.    NOT IN
  19.    (SELECT
  20.      c1.accountno
  21.     FROM
  22.      contact1 c1
  23.     JOIN contgrps cg
  24.      ON c1.accountno=cg.accountno
  25.      JOIN contgrps cg1
  26.        ON cg.userid = cg1.recid
  27.      WHERE
  28.       LEFT(cg1.Accountno,2) = '*M'
  29.       AND (cg1.REF = '2nd GoldMine Group Name'))
  30.  
Bookmark:

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • del.icio.us
  • Live
  • Google
  • Facebook
  • bodytext
  • StumbleUpon
  • Slashdot

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.  
Bookmark:

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • del.icio.us
  • Live
  • Google
  • Facebook
  • bodytext
  • StumbleUpon
  • Slashdot

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.  
Bookmark:

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • del.icio.us
  • Live
  • Google
  • Facebook
  • bodytext
  • StumbleUpon
  • Slashdot

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.  
Bookmark:

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • del.icio.us
  • Live
  • Google
  • Facebook
  • bodytext
  • StumbleUpon
  • Slashdot

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'
Bookmark:

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • del.icio.us
  • Live
  • Google
  • Facebook
  • bodytext
  • StumbleUpon
  • Slashdot

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

Bookmark:

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • del.icio.us
  • Live
  • Google
  • Facebook
  • bodytext
  • StumbleUpon
  • Slashdot

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.  
Bookmark:

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • del.icio.us
  • Live
  • Google
  • Facebook
  • bodytext
  • StumbleUpon
  • Slashdot

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. 3.331 seconds