SQL Learning Center - GoldMine http://www.crmsql.com Use SQL queries to get answers from GoldMine Mon, 01 Feb 2010 14:10:28 +0000 http://wordpress.org/?v=1.5.2 en History - No Notes for Date Range - Version 8.5 forward http://www.crmsql.com/?p=47 http://www.crmsql.com/?p=47#comments Thu, 14 Jan 2010 03:35:08 +0000 Doug Steinschneider MSSQL History http://www.crmsql.com/?p=47 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.        
]]> http://www.crmsql.com/?feed=rss2&p=47 History - No notes for Date Range for GoldMine Versions previous to 8.5 http://www.crmsql.com/?p=46 http://www.crmsql.com/?p=46#comments Thu, 14 Jan 2010 03:33:00 +0000 Doug Steinschneider MSSQL History http://www.crmsql.com/?p=46 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.  
]]> http://www.crmsql.com/?feed=rss2&p=46 History Activity Counts http://www.crmsql.com/?p=45 http://www.crmsql.com/?p=45#comments Fri, 06 Nov 2009 19:30:44 +0000 Doug Steinschneider MSSQL Counts MSSQL History http://www.crmsql.com/?p=45 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.  
]]> http://www.crmsql.com/?feed=rss2&p=45 More Than Two Matching Company Records Not In OrgChart http://www.crmsql.com/?p=43 http://www.crmsql.com/?p=43#comments Wed, 05 Aug 2009 23:46:01 +0000 Doug Steinschneider MSSQL Contacts http://www.crmsql.com/?p=43 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.        
]]> http://www.crmsql.com/?feed=rss2&p=43 Additional Contact Email http://www.crmsql.com/?p=42 http://www.crmsql.com/?p=42#comments Wed, 05 Aug 2009 21:35:45 +0000 Doug Steinschneider MSSQL Email http://www.crmsql.com/?p=42 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.        
]]> http://www.crmsql.com/?feed=rss2&p=42 Call Counts for One or More Users Per Month http://www.crmsql.com/?p=41 http://www.crmsql.com/?p=41#comments Fri, 24 Jul 2009 18:36:04 +0000 Doug Steinschneider MSSQL Counts MSSQL History http://www.crmsql.com/?p=41 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.  
]]> http://www.crmsql.com/?feed=rss2&p=41 Find Primary Email Addresses for State and Contact Type LIKE http://www.crmsql.com/?p=40 http://www.crmsql.com/?p=40#comments Mon, 27 Apr 2009 16:23:02 +0000 Doug Steinschneider MSSQL Email http://www.crmsql.com/?p=40 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.  
]]> http://www.crmsql.com/?feed=rss2&p=40 Contacts where GoldMine userid has more than 5 histories http://www.crmsql.com/?p=39 http://www.crmsql.com/?p=39#comments Sun, 08 Mar 2009 16:50:01 +0000 Doug Steinschneider MSSQL History http://www.crmsql.com/?p=39 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.  
]]> http://www.crmsql.com/?feed=rss2&p=39 Find Malformed Email Addresses http://www.crmsql.com/?p=38 http://www.crmsql.com/?p=38#comments Tue, 28 Oct 2008 21:31:12 +0000 Doug Steinschneider MSSQL Email http://www.crmsql.com/?p=38 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

]]> http://www.crmsql.com/?feed=rss2&p=38 Find all contacts sent a certain attachment http://www.crmsql.com/?p=37 http://www.crmsql.com/?p=37#comments Fri, 12 Sep 2008 02:11:47 +0000 Doug Steinschneider MSSQL History MSSQL Email http://www.crmsql.com/?p=37 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.  
]]> http://www.crmsql.com/?feed=rss2&p=37