SQL Learning Center - GoldMine

Use SQL queries to get answers from GoldMine

April 27, 2009

Find Primary Email Addresses for State and Contact Type LIKE

by Doug Steinschneider @ 11:23 am. Filed under MSSQL Email

Type: MSSQL
Description: Find Primary Email Addresses for State and Contact Type LIKE (type in whole or partial words between the percent signs)

  1.  
  2. SELECT
  3.  c1.contact
  4. ,cs.contsupref
  5. ,cs.accountno
  6. ,c1.lastname
  7. ,c1.key1 AS ContType
  8. FROM
  9.  contact1 c1
  10.  JOIN contsupp cs
  11.   ON c1.accountno=cs.accountno
  12. WHERE
  13.  cs.contact='E-mail Address'
  14.  AND SUBSTRING(cs.zip FROM 2 FOR 1) = '1'
  15.  AND c1.STATE IN ('MA')
  16.  AND (c1.key1 LIKE '%Instr%'
  17.       OR c1.key1 LIKE '%Stu%')
  18. ORDER BY
  19.  c1.lastname
  20.  
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

March 8, 2009

Contacts where GoldMine userid has more than 5 histories

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

Type: MSSQL
Description: Contacts where GoldMine userid has more than 5 histories

  1.  
  2.        
  3. SELECT
  4.  c1.company
  5. ,c1.contact
  6.  c1.accountno
  7. FROM
  8.  contact1 c1
  9. WHERE
  10.  c1.accountno IN
  11.  (SELECT
  12.    accountno
  13.   FROM
  14.    conthist
  15.   WHERE
  16.    userid='MASTER'
  17.   GROUP BY
  18.    accountno
  19.   ,userid
  20.   HAVING
  21.    COUNT(*) > 5)
  22.  
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

October 28, 2008

Find Malformed Email Addresses

by Doug Steinschneider @ 4:31 pm. Filed under MSSQL Email

Type: MSSQL
Description: Find Malformed Email Addresses
NOTE: Because this query is looking for special characters it doesn’t display properly in a web page - please cut and paste the text of the query from here: Find Malformed Email Addresses


SELECT
accountno
,cs.contsupref AS 'E-Mail Address'
FROM
contsupp cs
WHERE
(cs.CONTACT = 'E-mail Address')
AND (SELECT
CASE
WHEN cs.contsupref IS NULL
OR CHARINDEX('@.',cs.contsupref) > 0
OR CHARINDEX('.@',cs.contsupref) > 0
OR CHARINDEX('..',cs.contsupref) > 0
OR CHARINDEX('"', cs.contsupref) <> 0
OR CHARINDEX(’(', cs.contsupref) <> 0
OR CHARINDEX(’)', cs.contsupref) <> 0
OR CHARINDEX(’,', cs.contsupref) <> 0
OR CHARINDEX(’< ', cs.contsupref) <> 0
OR CHARINDEX(’>', cs.contsupref) <> 0
OR CHARINDEX(’;', cs.contsupref) <> 0
OR CHARINDEX(’:', cs.contsupref) <> 0
OR CHARINDEX(’[', cs.contsupref) <> 0
OR CHARINDEX(’]', cs.contsupref) <> 0
OR RIGHT(RTRIM(cs.contsupref),1) = ‘.’
OR CHARINDEX(’ ‘,LTRIM(RTRIM(cs.contsupref))) > 0
OR LEN(cs.contsupref)-1 < = CHARINDEX('.', cs.contsupref)
OR cs.contsupref LIKE '%@%@%'
OR cs.contsupref NOT LIKE '%@%.%' THEN 0
ELSE 1
END) = 0

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

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

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