Useful SQL Queries regarding Opportunity Management and related data

Version 1

    Details

    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. Click Query
    Button
    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) Opportunities closed today

    SELECT  OP.USERIDas 'User' ,OP.NAME,C1.COMPANYAS 'Company' ,OP.CLOSEAMTAS 'AMOUNT'

    ,CONVERT(VARCHAR(10),OP.CLOSEDDATE,101) AS 'Close Date' ,C1.KEY4AS 'Acct Manager' ,C1.KEY1AS 'Client Type' ,OP.STAGEas 'Result'

    FROM  OPMGR OP (NOLOCK)

    JOIN CONTACT1 C1 ON OP.ACCOUNTNO=C1.ACCOUNTNO 

    JOINCONTACT2 C2 ON C1.ACCOUNTNO=C2.ACCOUNTNO

    WHERE  OP.RECTYPELIKE 'OZ%'

    AND CONVERT(VARCHAR(10),OP.CLOSEDDATE,101) >= CONVERT(VARCHAR(10), GETDATE(),101)

    ORDER BYOP.USERID,C1.COMPANY, OP.STAGEDESC

     

    To change the date range, amend the GetDate() value

    For Example. Getdate()-30 will return all Opportunities closed in the last 30 days.

     


    (2) Opportunities closed in a certain quarter of a certain timeframe

     

    SELECT C1.KEY1 AS [Client Type] ,OP.USERID as [User],

    DATEPART(Q,OP.CLOSEDDATE) AS [Quarter] ,

    LEFT(OP.CLOSEDDATE,12) AS [Close Date] ,C1.COMPANY as [Company],OP.NAME as [Name]

    ,OP.CLOSEAMT as [Close Amount] ,OP.STAGE as [Result]

    FROM  OPMGR OP (NOLOCK) 

    JOIN CONTACT1 C1  ON OP.ACCOUNTNO=C1.ACCOUNTNO

    WHERE  OP.RECTYPE LIKE 'OZ%'

    AND DATEPART(Q,OP.CLOSEDDATE) = '<Enter Calendar Quarter Number>'  

    AND OP.CLOSEDDATE BETWEEN  '<Enter start of desired timeframe>' AND '<Enter end of desired timeframe>'

    GROUP BY

    C1.KEY1 ,OP.USERID ,DATEPART(Q,OP.CLOSEDDATE) ,OP.CLOSEDDATE

    ,C1.COMPANY ,OP.NAME ,OP.CLOSEAMT ,OP.ACCOUNTNO, OP.STAGE

    order by QUARTER, COMPANY, OP.STAGE desc

     

    To set the specific quarters change the highlighted values

    1st Quarter = '1'
    2nd Quarter = '2'
    3rd Quarter = '3'
    4th Quarter = '4'

    Enter start of desired timeframe = 'MM/DD/YY'
    Enter end of desired timeframe = 'MM/DD/YY'


    (3) Opportunities closed per quarter of specific year

     

    SELECT C1.KEY1 AS [Client Type] ,OP.USERID as [User],

    DATEPART(Q,OP.CLOSEDDATE) AS [Quarter] ,

    LEFT(OP.CLOSEDDATE,12) AS [Close Date] ,C1.COMPANY as [Company],OP.NAME as [Name]

    ,OP.CLOSEAMT as [Close Amount] ,OP.STAGE as [Result]

    FROM  OPMGR OP (NOLOCK) 

    JOIN CONTACT1 C1  ON OP.ACCOUNTNO=C1.ACCOUNTNO

    WHERE  OP.RECTYPE LIKE 'OZ%'

    AND DATEPART(YYYY,OP.CLOSEDDATE) = '<Enter desired year>'

    GROUP BY

    C1.KEY1 ,OP.USERID ,DATEPART(Q,OP.CLOSEDDATE) ,OP.CLOSEDDATE

    ,C1.COMPANY ,OP.NAME ,OP.CLOSEAMT ,OP.ACCOUNTNO, OP.STAGE

    order by QUARTER, COMPANY, OP.STAGE desc

     

     

    (4) Opportunities closed per quarter of current year

     

    SELECT C1.KEY1 AS [Client Type] ,OP.USERID as [User],

    DATEPART(Q,OP.CLOSEDDATE) AS [Quarter] ,

    LEFT(OP.CLOSEDDATE,12) AS [Close Date] ,C1.COMPANY as [Company],OP.NAME as [Name]

    ,OP.CLOSEAMT as [Close Amount] ,OP.STAGE as [Result]

    FROM  OPMGR OP (NOLOCK)  

    JOIN CONTACT1 C1  ON OP.ACCOUNTNO=C1.ACCOUNTNO

    WHERE  OP.RECTYPE LIKE 'OZ%'

    AND DATEPART(YYYY,OP.CLOSEDDATE) = DATEPART(YYYY, GETDATE()) 

    GROUP BY

    C1.KEY1 ,OP.USERID ,DATEPART(Q,OP.CLOSEDDATE) ,OP.CLOSEDDATE

    ,C1.COMPANY ,OP.NAME ,OP.CLOSEAMT ,OP.ACCOUNTNO, OP.STAGE

    order by QUARTER, COMPANY, OP.STAGE desc

     

     

    (4) Open Opportunities including related Influencers


    SELECT
    O1.NAME AS Opportunity, O1.COMPANY, O1.CONTACT,O1.STAGE, O1.CYCLE AS Code, O1.FORAMT AS Forecast, O1.FORPROB AS Probability, O1.CLOSEAMT AS [Closed Amount], datediff(day,o1.startdate-1,getdate()) as Age, O1.STARTDATE, O1.CLOSEDDATE, O1.CLOSEBY as [Target Date], O1.STATUS, O1.USERID, O1.SOURCE, O2.CONTACT AS Influencer
    FROM OPMGR O1
    JOIN OPMGR O2
      ON O1.RECID = O2.OPID
    WHERE RTRIM(O1.RECTYPE) = 'O'
    AND O2.RECTYPE = 'OC'
    ORDER BY O1.OPID


    Additional note:
    - Any change for the opportunities related must be referenced as O1 for  example when looking only for open opportunities for a certain user then the where clause needs an amendment like

    WHERE RTRIM(O1.RECTYPE) = 'O'
    AND O2.RECTYPE = 'OC'
    AND O1.USERID = 'MASTER'

    or querying also for closed opportunities regardless if won or lost

    WHERE RTRIM(O1.RECTYPE) in ('O', 'OZ')
    AND O2.RECTYPE = 'OC'