SQL Learning Center - GoldMine

Use SQL queries to get answers from GoldMine

January 13, 2010

History - No Notes for Date Range - Version 8.5 forward

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

Type: MSSQL
Description: History with No Note for Date Range for GoldMine Version 8.5 Forward

  1.  
  2.        
  3. SELECT
  4.   ch.REF
  5.  ,ch.ondate
  6.  ,CAST(CAST(ch.notes AS VARBINARY(MAX))AS VARCHAR(MAX)) AS Notes
  7. FROM
  8.  conthist ch
  9. WHERE
  10.  DATALENGTH(CAST(CAST(notes AS VARBINARY(MAX))AS VARCHAR(MAX))) < 1
  11.  AND ch.ondate BETWEEN '01/01/2009' AND '12/31/2009'
  12.        
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 - No notes for Date Range for GoldMine Versions previous to 8.5

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

Type: MSSQL
Description: History with No Note for Date Range for GoldMine before Version 8.5

  1.  
  2. SELECT
  3.   ch.REF
  4.  ,ch.ondate
  5.  ,ch.notes
  6. FROM
  7.  conthist ch
  8. WHERE
  9.  DATALENGTH(ch.notes) < 1
  10.  AND ch.ondate BETWEEN '01/01/2009' AND '12/31/2009'
  11.  
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

November 6, 2009

History Activity Counts

by Doug Steinschneider @ 2:30 pm. Filed under MSSQL Counts, MSSQL History

Type: MSSQL
Description: Get counts of History Activities by type for a user

  1.  
  2.        
  3. SELECT
  4.  CASE LEFT(ch.rectype,2)
  5.   WHEN 'CO' THEN 'Call Out'
  6.   WHEN 'CI' THEN 'Call In'
  7.   WHEN 'CC' THEN 'Call Back'
  8.   WHEN 'CM' THEN 'Returned Message'
  9.   WHEN 'MO' THEN 'Email Out'
  10.   WHEN 'MI' THEN 'Email In'
  11.   WHEN 'A '  THEN 'Appointment'
  12.   WHEN 'N '  THEN 'Next Action'
  13.   WHEN 'O '  THEN 'Other Action'
  14.   WHEN 'L '  THEN 'Form'
  15.  ELSE
  16.   ch.rectype
  17.  END Activity
  18. ,COUNT(*)
  19. FROM
  20.  conthist ch (NOLOCK)
  21.  JOIN contact1 c1
  22.   ON ch.accountno=c1.accountno
  23. WHERE
  24.  srectype IN ('C','A','E','M' )
  25.  AND ch.userid='DCS'
  26. GROUP BY
  27.  CASE LEFT(ch.rectype,2)
  28.   WHEN 'CO' THEN 'Call Out'
  29.   WHEN 'CI' THEN 'Call In'
  30.   WHEN 'CC' THEN 'Call Back'
  31.   WHEN 'CM' THEN 'Returned Message'
  32.   WHEN 'MO' THEN 'Email Out'
  33.   WHEN 'MI' THEN 'Email In'
  34.   WHEN 'A '  THEN 'Appointment'
  35.   WHEN 'N '  THEN 'Next Action'
  36.   WHEN 'O '  THEN 'Other Action'
  37.   WHEN 'L '  THEN 'Form'
  38.  ELSE
  39.   ch.rectype
  40.  END
  41.  
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 5, 2009

More Than Two Matching Company Records Not In OrgChart

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

Type: MSSQL
Description: More Than Two Matching Company Records Not In OrgChart - We use GoldBox to add contact records automatically to company name based orgcharts. If users enter a record with no value in contact it doesn’t add the record. We use this to find any records where there are two matching company names but they have not been put together into an OrgChart based on company name.

  1.  
  2. SELECT
  3.  c1.company
  4. ,c1.contact
  5. ,c1.accountno
  6. FROM
  7.  contact1 c1
  8. WHERE
  9.  c1.company > ''
  10.  AND c1.company IN
  11.   (SELECT
  12.     company
  13.    FROM
  14.     contact1
  15.    WHERE
  16.     company > ''
  17.    GROUP BY
  18.     company
  19.    HAVING
  20.     COUNT(company) > 1)
  21.   AND c1.accountno NOT IN
  22.   (SELECT
  23.     accountno
  24.    FROM
  25.     contsupp
  26.    WHERE
  27.     rectype = 'O')
  28.        
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

Additional Contact Email

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

Type: MSSQL
Description: This uses the ISNULL function to correctly bring back emails that fit inside contsupp.contsupref and the longer ones that spill over in to contsupp.address1

  1.  
  2. SELECT
  3.  c1.key1
  4. ,cs.contact
  5. ,ISNULL(cs1.contsupref,'') + ISNULL(cs1.address1,'') AS email
  6. FROM
  7.  contact1 c1
  8.  JOIN contsupp cs
  9.   ON c1.accountno=cs.accountno
  10.   JOIN contsupp cs1 ON cs.recid=cs1.linkacct
  11. WHERE
  12.  cs.rectype='C'
  13.  AND cs1.contact='E-Mail Address'
  14.  AND cs1.rectype = 'P'  
  15.        
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

July 24, 2009

Call Counts for One or More Users Per Month

by Doug Steinschneider @ 1:36 pm. Filed under MSSQL Counts, MSSQL History

Type: MSSQL
Description: Call Counts for One or More Users Per Month

  1.  
  2.        
  3. SELECT
  4.  ch.userid
  5. ,DATENAME(m,ch.ondate) AS Call_Month
  6. ,DATEPART(m,ch.ondate) AS Month_Num
  7. ,COUNT(*) AS Call_Count
  8. FROM conthist ch
  9. WHERE
  10.  ch.userid = 'JSMITH'
  11.  AND ch.rectype LIKE 'C%'
  12.  AND YEAR(ch.ondate) = '2009'
  13. GROUP BY
  14.  ch.userid
  15. ,DATENAME(m,ch.ondate)
  16. ,DATEPART(m,ch.ondate)
  17. ORDER BY
  18.  DATEPART(m,ch.ondate)
  19.  
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

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

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