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.        

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.  

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.  

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.  

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.  

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.  

August 13, 2008

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.  

May 28, 2008

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.  

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