SQL Learning Center - GoldMine

Use SQL queries to get answers from GoldMine

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

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