Useful SQL Queries regarding Cases / Service Center 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) Overview about Cases versus Case templates
     
    SELECT '# of Cases' AS [Description], ' ' AS [STATUS], COUNT(RECID) AS [Amount] FROM CASES WHERE STATUS in ('5','4','3','2','1')
    UNION
    SELECT '# of Case templates' AS [Description], ' ' AS [STATUS], COUNT(RECID) AS [Amount] FROM CASES WHERE Is_template ='1'
    UNION
    SELECT 'Records in CASES table' AS [Description], ' ' AS [STATUS], COUNT(RECID) AS [Amount] FROM CASES
    ORDER BY COUNT(RECID) DESC, [Description] ASC


    (2) Overview about Cases for each status (does not contain Case templates)

    SELECT 'Open/Assigned' AS [Description], '1' AS [STATUS],COUNT(RECID)AS [Amount]  FROM CASES WHERE STATUS ='1'
    UNION
    SELECT 'Re-assigned' AS [Description],'2' AS [STATUS], COUNT(RECID) AS [Amount] FROM CASES WHERE STATUS ='2'
    UNION
    SELECT 'Escalated' AS [Description],'3' AS [STATUS], COUNT(RECID)AS [Amount] FROM CASES WHERE STATUS ='3'
    UNION
    SELECT 'Resolved' AS [Description], '4' AS [STATUS], COUNT(RECID) AS [Amount] FROM CASES WHERE STATUS ='4'
    UNION
    SELECT 'Abandoned' AS [Description],'5' AS [STATUS], COUNT(RECID)AS [Amount] FROM CASES WHERE STATUS ='5'
    UNION
    SELECT '# of Cases' AS [Description], ' ' AS [STATUS], COUNT(RECID) AS [Amount] FROM CASES WHERE STATUS in ('5','4','3','2','1')
    ORDER BY COUNT(RECID) DESC, [Description] ASC

    The current query orders results by their amount per status descending


    (3) Overview about Cases per user, per status

    Status 1 = Assigned
    Status 2 = Re-Assigned
    Status 3 = Escalated
    Status 4 = Resolved
    Status 5 = Abandoned

    SELECT COUNT(*) AS AMOUNT, OWNER, STATUS  FROM CASES WHERE IS_TEMPLATE = '0' GROUP BY OWNER, STATUS ORDER BY STATUS ASC, AMOUNT DESC


    (4) Overview about Cases Open and Closed

    Open Cases: Assigned, Re-Assigned, Escalated
    Closed Cases: Resolved, Abandoned 

    SELECT
    (SELECT COUNT(1)  FROM CASES WHERE STATUS IN ('5','4','3','2','1') AND IS_TEMPLATE ='0') AS [# TOTAL], 
    (SELECT COUNT(1) FROM CASES WHERE STATUS IN ('3','2','1') AND IS_TEMPLATE ='0')AS [# OPEN] ,
     (SELECT COUNT(1) FROM CASES WHERE STATUS IN ('5','4')AND IS_TEMPLATE ='0')AS [# CLOSED]
      
    (5) Overview about Cases Closed per Year, Quarter, Company, Contact
     
    Closed Cases: Resolved, Abandoned 

    SELECT C1.COMPANY AS[COMPANY], C1.CONTACT AS [CONTACT], COUNT(CA.RECID) AS [AMOUNT],
    DATEPART(Q,CA.RESOLVED_DATE) AS [QUARTER] ,
    DATEPART(YYYY,CA.RESOLVED_DATE) AS [YEAR]
    FROM  CASES CA (NOLOCK) 
    JOIN CONTACT1 C1  ON CA.ACCOUNTNO=C1.ACCOUNTNO
    WHERE  CA.STATUS IN ('4', '5')
    -- AND DATEPART(YYYY,CA.RESOLVED_DATE) = '2015'
    GROUP BY
    C1.COMPANY, C1.CONTACT ,DATEPART(Q,CA.RESOLVED_DATE), DATEPART(YYYY,CA.RESOLVED_DATE)
    ORDER BY YEAR, QUARTER

    If applicable restrict by a certain year by reset
    and adjust the comment

    (6) Overview about Cases Closed per Year, Quarter, User - (Top Performer)

    Closed Cases: Resolved, Abandoned

    SELECT COUNT(CA.RECID) AS [AMOUNT], CA.OWNER AS [USER],
    DATEPART(Q,CA.RESOLVED_DATE) AS [QUARTER], DATEPART(YYYY,CA.RESOLVED_DATE) AS [YEAR]
    FROM  CASES CA (NOLOCK) 
    WHERE  CA.STATUS IN ('4', '5')
    -- AND DATEPART(YYYY,CA.RESOLVED_DATE) = '2015'
    GROUP BY
    CA.OWNER ,DATEPART(Q,CA.RESOLVED_DATE), DATEPART(YYYY,CA.RESOLVED_DATE) 
    ORDER BY YEAR DESC, QUARTER ASC, AMOUNT DESC

    If applicable restrict by a certain year by reset and adjust the comment

    (7) Overview about Cases Open per User - (Workload)

    Open Cases: Assigned, Re-Assigned, Escalated

    SELECT CA.OWNER AS [USER],
    DATEPART(Q,CA.CREATED_DATE) AS [QUARTER],
    DATEPART(YYYY,CA.CREATED_DATE) AS [YEAR],
    COUNT(CA.RECID) AS [AMOUNT]
    FROM  CASES CA (NOLOCK) 
    WHERE  CA.STATUS IN ('1', '2', '3')
    -- AND DATEPART(YYYY,CA.CREATED_DATE) = '2015'
    GROUP BY
    CA.OWNER, DATEPART(Q,CA.CREATED_DATE), DATEPART(YYYY,CA.CREATED_DATE) 
    ORDER BY YEAR DESC, QUARTER ASC, AMOUNT DESC

    If applicable restrict by a certain year by reset and adjust the comment

    (8) Case submissions per time frame

    (a) per Quarter/Year
    SELECT DATEPART(Q,CA.CREATED_DATE) AS [QUARTER],
    DATEPART(YYYY,CA.CREATED_DATE) AS [YEAR],
    COUNT(CA.RECID) AS [AMOUNT]
    FROM  CASES CA (NOLOCK) 
    WHERE  CA.IS_TEMPLATE ='0'
    -- AND DATEPART(YYYY,CA.CREATED_DATE) = '2015'
    GROUP BY
    DATEPART(Q,CA.CREATED_DATE), DATEPART(YYYY,CA.CREATED_DATE) 
    ORDER BY YEAR DESC, QUARTER ASC, AMOUNT DESC

    If applicable restrict by a certain year by reset and adjust the comment

    (b) per Year
    SELECT DATEPART(YYYY,CA.CREATED_DATE) AS [YEAR],
    COUNT(CA.RECID) AS [SUBMISSIONS]
    FROM  CASES CA (NOLOCK) 
    WHERE  CA.IS_TEMPLATE ='0'
    GROUP BY
    DATEPART(YYYY,CA.CREATED_DATE) 
    ORDER BY YEAR DESC, SUBMISSIONS DESC