http://www.crmsql.com Use SQL queries to get answers from GoldMine Mon, 01 Feb 2010 14:10:28 +0000 http://backend.userland.com/rss092 en Type: MSSQL Description: History with No Note for Date Range for GoldMine Version 8.5 Forward           SELECT   ch.REF  ,ch.ondate  ,CAST(CAST(ch.notes AS VARBINARY(MAX))AS VARCHAR(MAX)) AS Notes FROM  conthist ch WHERE  DATALENGTH(CAST(CAST(notes AS VARBINARY(MAX))AS VARCHAR(MAX))) < 1  AND ch.ondate BETWEEN '01/01/2009' AND '12/31/2009'         http://www.crmsql.com/?p=47 Type: MSSQL Description: History with No Note for Date Range for GoldMine before Version 8.5   SELECT   ch.REF  ,ch.ondate  ,ch.notes FROM  conthist ch WHERE  DATALENGTH(ch.notes) < 1  AND ch.ondate BETWEEN '01/01/2009' AND '12/31/2009'   http://www.crmsql.com/?p=46 Type: MSSQL Description: Get counts of History Activities by type for a user           SELECT  CASE LEFT(ch.rectype,2)   WHEN 'CO' THEN 'Call Out'   WHEN 'CI' THEN 'Call In'   WHEN 'CC' THEN 'Call Back'   WHEN 'CM' THEN 'Returned Message'   WHEN 'MO' THEN 'Email Out'   WHEN 'MI' THEN 'Email In'   WHEN 'A ... http://www.crmsql.com/?p=45 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 ... http://www.crmsql.com/?p=43 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   SELECT  c1.key1 ,cs.contact ,ISNULL(cs1.contsupref,'') + ISNULL(cs1.address1,'') AS email FROM  contact1 c1  JOIN contsupp cs   ON c1.accountno=cs.accountno   JOIN contsupp cs1 ON cs.recid=cs1.linkacct WHERE  cs.rectype='C'  AND cs1.contact='E-Mail Address'  AND cs1.rectype = 'P'           ... http://www.crmsql.com/?p=42 Type: MSSQL Description: Call Counts for One or More Users Per Month           SELECT  ch.userid ,DATENAME(m,ch.ondate) AS Call_Month ,DATEPART(m,ch.ondate) AS Month_Num ,COUNT(*) AS Call_Count FROM conthist ch WHERE  ch.userid = 'JSMITH'  AND ch.rectype LIKE 'C%'  AND YEAR(ch.ondate) = '2009' GROUP BY  ch.userid ,DATENAME(m,ch.ondate) ,DATEPART(m,ch.ondate) ORDER BY  DATEPART(m,ch.ondate)   http://www.crmsql.com/?p=41 Type: MSSQL Description: Find Primary Email Addresses for State and Contact Type LIKE (type in whole or partial words between the percent signs)   SELECT  c1.contact ,cs.contsupref ,cs.accountno ,c1.lastname ,c1.key1 AS ContType FROM  contact1 c1  JOIN contsupp cs   ON c1.accountno=cs.accountno WHERE  cs.contact='E-mail Address'  AND SUBSTRING(cs.zip FROM 2 FOR 1) = '1'  AND c1.STATE IN ('MA')  AND (c1.key1 LIKE '%Instr%'       OR c1.key1 LIKE '%Stu%') ORDER ... http://www.crmsql.com/?p=40 Type: MSSQL Description: Contacts where GoldMine userid has more than 5 histories           SELECT  c1.company ,c1.contact  c1.accountno FROM  contact1 c1 WHERE  c1.accountno IN  (SELECT    accountno   FROM    conthist   WHERE    userid='MASTER'   GROUP BY    accountno   ,userid   HAVING    COUNT(*) > 5)   http://www.crmsql.com/?p=39 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') ... http://www.crmsql.com/?p=38 Type: MSSQL Description: Find all contacts sent a certain attachment   SELECT  c1.contact ,c1.company ,ch.rectype ,mb.maildate ,c1.accountno FROM  contact1 c1  JOIN conthist ch  ON c1.accountno=ch.accountno  JOIN mailbox mb   ON ch.recid = mb.linkrecid WHERE  mb.maildate > '11/01/2007'  AND mb.rfc822 LIKE '%Put attachment filename here%'   http://www.crmsql.com/?p=37