SQL Learning Center - GoldMine

Use SQL queries to get answers from GoldMine

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.  

May 30, 2008

Counts of Contact Type (key1) for a Specific List of Contact Types

by Doug Steinschneider @ 7:44 am. Filed under MSSQL Counts

Type: MSSQL
Description: This query will give you counts for a list of Contact Types you give it in the WHERE portion

  1.  
  2. SELECT
  3.  COUNT(c1.accountno) AS Investor_Count
  4. ,c1.key4 AS AcctMgr
  5. ,c1.key1
  6. FROM
  7.  contact1 c1 (NOLOCK)
  8. WHERE
  9.  c1.accountno IN (
  10.  SELECT
  11.   c1.accountno
  12.  FROM
  13.   contact1 c1
  14.    JOIN conthist ch
  15.    ON c1.accountno=ch.accountno
  16.  WHERE
  17.   ch.userid = c1.key4
  18.   AND c1.key1 IN (
  19.   'Broker'
  20.  ,'Charity'
  21.  ,'Consultant'
  22.  ,'Corporate Pension'
  23.  ,'Endowment'
  24.  ,'Family Office'
  25.  ,'Foundation'
  26.  ,'High Net Worth'
  27.  ,'Insurance Company'
  28.  ,'Investment Advisor'
  29.  ,'Investment Manager'
  30.  ,'Investor'
  31.  ,'Law Firm'
  32.  ,'Manager'
  33.  ,'Other'
  34.  ,'Pension'
  35.  ,'Prime Broker'
  36.  ,'Private Bank'
  37.  ,'Private Equity'
  38.  ,'Public Pension'
  39.  ,'Trust'
  40.  ,'Union Pension'
  41.  ,'University'
  42.  ,'Wealth Management'
  43.  ,''))
  44. GROUP BY
  45.  c1.key4
  46. ,c1.key1
  47.  

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

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

21 queries. 0.162 seconds