Useful SQL Queries regarding History related information within GoldMine

Version 1

    Details

    Useful SQL Queries regarding History related information within GoldMine

    GoldMine Technical Support does  not assist in creating (complex) SQL queries, but the below queries were often requested by customers and  also are often used by GoldMine Technical Support during investigations

    Please keep in mind that all queries are always only provided as-is and you may want or need to adjust additional information you may want to see or not see.

    All SQL queries are SELECT statements and will not change any  data within the GoldMine database and they can either be performed from  within GoldMine (*) or from the SQL Server Management Studio

    (*) the GoldMine SQL Query feature does not respect Record ownership and curtaining

    A. Via GoldMine's SQL query feature

    1. Tools >> SQL Query
    2. Copy and paste the desired SELECT query from below into the Query pane
    3. Button Query
    >> the result set will appear in the lower pane
    4. the query can be saved via the Save button for a later use

    B. via SQL Server Management Studio
    1. Open the SQL Server Management Studio
    2. Button New Query
    3. Make sure to select the (desired) GoldMine database from the database drop down
    4.
    Copy and paste the desired SELECT query from below into the Query pane
    5. run the query via the button Execute (red exclamation mark)
    >> the result set will appear in the lower pane


    Resolution

     

    (1) Contacts with no History

     

    SELECT COMPANY,CONTACT, ACCOUNTNO

    FROM CONTACT1

    WHERE ACCOUNTNO NOT IN

    (SELECT ACCOUNTNO FROMCONTHIST)

     

    (2) Contacts with no history from a specific date

     

    SELECT  CONTACT1.ACCOUNTNO, CONTACT1.CONTACT, CONTACT1.COMPANY

    FROM CONTACT1

    WHERE CONTACT1.ACCOUNTNO NOT IN

    (SELECT ACCOUNTNO FROMCONTHIST WHERE CONTHIST.ONDATE >= '2012/04/12')

    Date format is YYYY/MM/DD

     

    (3) Count of history items for all users in a given beginning date until today or a specific date range

     

    SELECT CONTHIST.USERID, COUNT(*) AS [# OF ACTIVITIES]

    FROM CONTHIST

    WHERE CONTHIST.ONDATE BETWEEN '2012/01/01' AND GETDATE()

    GROUP BY CONTHIST.USERID

     

    Replace GETDATE() with a second date in format 'YYYY/MM/DD' for a specific certain date range . This must be larger / later than the first date

     

    (4) Contacts that have history in the last year (365 days)

     

    SELECT ACCOUNTNO, CONTACT, COMPANY

    FROM CONTACT1

    WHERE ACCOUNTNO NOT IN (SELECT ACCOUNTNO FROM CONTHIST WHERE

    (ONDATE)>=GETDATE()-365)

    ORDER BY COMPANY



     

    (5) List of historical activities with removing the (oc:<Contact Name>) from the history reference

     

    SELECT CONVERT(VARCHAR(X), CONTHIST.ONDATE, YYY) AS ONDATE,

    SUBSTRING(REF,1,CHARINDEX(' (OC:',REF)) AS REF,

    CONTACT1.COMPANY,CONTACT1.CONTACT

    FROM   CONTACT1,CONTHIST

    WHERE CONTACT1.ACCOUNTNO=CONTHIST.ACCOUNTNO

     

    Replace VARCHAR(X) with the desired amount of displayed characters for the date format – corresponding to Date format YYY which must be also replaced.

     

    Examples:

     

    A.   Date Format - May 13 2013

    SELECT CONVERT(VARCHAR(11), CONTHIST.ONDATE, 100) AS ONDATE,

    SUBSTRING(REF,1,CHARINDEX(' (OC:',REF)) AS REF,

    CONTACT1.COMPANY,CONTACT1.CONTACT

    FROM   CONTACT1,CONTHIST

    WHERE CONTACT1.ACCOUNTNO=CONTHIST.ACCOUNTNO

     

    B.   Date Format - 05/13/2013 (UK format)

    SELECT CONVERT(VARCHAR(10), CONTHIST.ONDATE, 103) AS ONDATE,

    SUBSTRING(REF,1,CHARINDEX(' (OC:',REF)) AS REF,

    CONTACT1.COMPANY,CONTACT1.CONTACT

    FROM   CONTACT1,CONTHIST

    WHERE CONTACT1.ACCOUNTNO=CONTHIST.ACCOUNTNO

     

    C.  Date Format - 05/13/2013 (US format)

     

    SELECT CONVERT(VARCHAR(10), CONTHIST.ONDATE, 103) AS ONDATE,

    SUBSTRING(REF,1,CHARINDEX(' (OC:',REF)) AS REF,

    CONTACT1.COMPANY,CONTACT1.CONTACT

    FROM   CONTACT1,CONTHIST

    WHERE CONTACT1.ACCOUNTNO=CONTHIST.ACCOUNTNO

     

    (6) Finding the last time a contact record was visited

    SELECT CONTACT1.COMPANY, CONTACT1.CONTACT, CONTHIST.USERID, CONVERT(VARCHAR(10), CONTHIST.ONDATE, 103) AS ONDATE, CONTHIST.REF

    FROM CONTACT1, CONTHIST

    WHERE CONTACT1.ACCOUNTNO=CONTHIST.ACCOUNTNO AND CONTHIST.RECID IN

    (SELECT MAX(RECID) FROM CONTHIST WHERE SRECTYPE='A'

    GROUP BY ACCOUNTNO)

    ORDER BY USERID, ONDATE

     

    For date formatting options refer to List of historical activities with removing the (oc:<Contact Name>) from the history reference and Date Formatting in MS SQL

     

     


     

     

    (7) Top 150 of Records with highest amount of History records

     

    Select Top 150 COUNT(*)as [# OF ACTIVITIES], ACCOUNTNO from CONTHIST group by ACCOUNTNO order by [# OF ACTIVITIES]desc

     

     


    (8) Percentage of History for the contact with highest amount of records 

     
    select
    top 150 Accountno, (count(*) * 100.0 / sum(count(*)) over()) as percentage

    from CONTHIST

    group by Accountno

    order by percentage desc

     

     

    (9) Percentage for Activity types in History  

     


    select SRECTYPE, (count(*) * 100.0 / sum(count(*)) over()) as percentage

    from CONTHIST

    group by SRECTYPE

    order by percentage desc