SQL Learning Center - GoldMine

Use SQL queries to get answers from GoldMine

May 22, 2008

Main Contact, Userdefined Field and Email Address

by Doug Steinschneider @ 1:42 pm. Filed under MSSQL Email

Description: This query returns main contact, userdefined field and primary email address from contsupp for key1 (Contact Type) equals ‘Customer’

  1.  
  2. SELECT
  3.   c1.contact
  4. , c1.company
  5. , c1.key1
  6. , c1.key5
  7. , c2.userdef01
  8. , ISNULL(cs.contsupref,'') + ISNULL(cs.address1,'') AS email
  9. FROM
  10.  contact1 (NOLOCK)
  11.   JOIN  contact2
  12.   ON c1.accountno=c2.accountno
  13.    JOIN  contsupp cs
  14.    ON c1.accountno=cs.accountno
  15. WHERE
  16.  c1.key1 = 'Customer'
  17.  AND cs.contact = 'E-mail Address'
  18.  AND cs.zip LIKE '_1__'
  19.  

Customers and Prospect with no callback scheduled

by Doug Steinschneider @ 12:29 pm. Filed under MSSQL Calendar

Customers and Prospect with no callback scheduled

Description: This Query assumes you use key1 to track Contact Type such as ‘Prospect’ or ‘Customer’ and key4 for the rep’s userid

  1.  
  2. SELECT
  3.  c1.contact
  4. ,c1.company
  5. ,c1.key1 AS ContactType
  6. ,c1.key4 AS Rep
  7. ,c1.accountno
  8. FROM
  9.  contact1 c1
  10. WHERE
  11.  c1.key1 IN ('Prospect','Customer')
  12.  AND c1.key4 = 'Reps userid here'
  13.  AND c1.accountno NOT IN
  14.  (SELECT
  15.    accountno
  16.   FROM
  17.    cal
  18.   WHERE
  19.    rectype LIKE 'C%'
  20.    AND ondate > GETDATE())
  21.  

May 19, 2008

Most recent history for each assigned account

by Doug Steinschneider @ 3:19 pm. Filed under MSSQL History

Description
This query assumes that you have assigned rep by putting their GoldMine userid in key4. It shows the one most recent history for each assigned contact. In this case the rep is ‘DCS’

  1.  
  2. SELECT
  3.  c1.contact
  4. ,c1.company
  5. ,c1.city
  6. ,c1.STATE
  7. ,c1.country
  8. ,CASE LEFT(ch.rectype,2)
  9.   WHEN 'CO' THEN 'Call Out'
  10.   WHEN 'CI' THEN 'Call In'
  11.   WHEN 'CC' THEN 'Call Back'
  12.   WHEN 'CM' THEN 'Returned Message'
  13.   WHEN 'MO' THEN 'Email Out'
  14.   WHEN 'MI' THEN 'Email In'
  15.   WHEN 'A '  THEN 'Appointment'
  16.   WHEN 'N '  THEN 'Next Action'
  17.   WHEN 'O '  THEN 'Other Action'
  18.   WHEN 'L '  THEN 'Form'
  19.  ELSE
  20.   ch.rectype
  21.  END Activity
  22. ,CONVERT(VARCHAR(10),ch.ondate,101) DATE
  23. ,ch.REF
  24. ,ch.notes
  25. ,ch.accountno
  26. ,c1.accountno
  27. FROM
  28.  conthist ch (NOLOCK)
  29.  JOIN contact1 c1
  30.   ON ch.accountno=c1.accountno
  31. WHERE
  32.  srectype IN ('C','A','E','M' )
  33.  AND LEFT(c1.key4,3) = 'DCS'
  34.  AND ch.recid IN
  35.    (SELECT TOP 1 recid
  36.     FROM conthist
  37.     WHERE
  38.      (accountno=ch.accountno)
  39.       AND srectype IN ('C','A','E','M','T')
  40.       AND userid = 'DCS'
  41.     ORDER BY
  42.      ondate
  43.     ,recid DESC)
  44. ORDER BY
  45.  c1.company
  46.  

gm mssql most recent history

May 18, 2008

Create table with GoldMine Field Usage Counts

by Doug Steinschneider @ 1:46 pm. Filed under MSSQL Query Analyzer

Description:
Run this only in Query Analzyer to create a table called ‘gm_userdef_usage_count’ which holds a row for each field and a count for how many times it has been used.

  1.  
  2. IF EXISTS(SELECT name FROM sysobjects WHERE name = 'gm_userdef_usage_count' AND type = 'U')
  3.      DROP TABLE gm_userdef_usage_count
  4.        
  5. CREATE TABLE gm_userdef_usage_count
  6. (    FieldName    VARCHAR(50),
  7.     RecCount    INT    )
  8.        
  9. --Declare cursor
  10. DECLARE curs CURSOR
  11. GLOBAL
  12. SCROLL
  13. STATIC
  14. FOR
  15. SELECT Field_Name,Field_Type FROM contudef WHERE dbfname ='CONTACT2' and field_name like 'U%'
  16. go
  17.        
  18. --Declare variables
  19. DECLARE @fldname VARCHAR(50)
  20. DECLARE @fldtype VARCHAR(1)
  21. DECLARE @temp VARCHAR(150)
  22. DECLARE @MAX INT
  23. DECLARE @counter INT
  24.        
  25. SET @counter = 1
  26. SELECT @MAX = COUNT(field_name) FROM contudef WHERE dbfname ='CONTACT2' and field_name like 'U%'
  27. --Open cursor and fetch record
  28. OPEN curs
  29. FETCH NEXT FROM curs INTO @fldname,@fldtype
  30.        
  31. --loop through them all
  32. WHILE @counter < = @MAX
  33. BEGIN
  34.     SELECT @counter = @counter+1
  35.     --print @fldname
  36.     IF @fldtype = 'C' or  @fldtype = 'D'
  37.         SET @temp = 'Insert into gm_userdef_usage_count Select ' + CHAR(39) + @fldname + CHAR(39) + ', count(accountno) from contact2 where ' + @fldname + ' is not null '
  38.     ELSE
  39.         SET @temp = 'Insert into gm_userdef_usage_count Select ' + CHAR(39) + @fldname + CHAR(39) + ', count(accountno) from contact2 where ' + @fldname + ' = 0 '
  40.     EXEC(@temp)
  41.     FETCH NEXT FROM curs INTO @fldname,@fldtype
  42. END
  43.        
  44. CLOSE curs
  45.        
  46. DEALLOCATE curs
  47.  

UserDef Fields by Screen

by Doug Steinschneider @ 1:34 pm. Filed under GoldMine dBase SQL Qry, Simple Counts

Description:This query is for a dBase hosted GoldMine. It returns the field names organized by field screen.

SELECT
 f52.label AS ScreenView
,f51.label AS ScreenName
,f51.fldname AS Fieldname
,f51.fldpos
FROM fields5 f51
 JOIN fields5 f52
  ON f51.viewid = f52.viewid
WHERE
 SUBSTRING(fldname FROM 1 FOR 1) = 'U'
 AND f52.fcol IS NULL
 AND f52.label = 'Tech Support Screen'
GROUP BY
 f52.label
,f51.label
,f51.fldname
,f51.fldpos
ORDER BY
 f51.fldpos

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):

Next Entries »

21 queries. 0.177 seconds