/bundles/itninjaweb/img/Breadcrumb_cap_w.png
(1) tickets raised in a particular Queue for a specific Category
(2) both active and archived


2 Comments   [ - ] Hide Comments

Comments

  • Try this

    SELECT HD_TICKET.ID,
    HD_CATEGORY.NAME AS CATEGORY,
    Q.NAME AS QUEUE_NAME
    FROM HD_TICKET
    JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
    JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID
    WHERE (HD_TICKET.HD_QUEUE_ID = 1)
    AND (HD_CATEGORY.NAME LIKE 'Software%')

    UNION ALL

    SELECT HD_ARCHIVE_TICKET.ID,
    HD_CATEGORY.NAME AS CATEGORY,
    Q.NAME AS QUEUE_NAME
    FROM HD_ARCHIVE_TICKET
    JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_ARCHIVE_TICKET.HD_CATEGORY_ID)
    JOIN HD_QUEUE Q ON Q.ID = HD_ARCHIVE_TICKET.HD_QUEUE_ID
    WHERE (HD_ARCHIVE_TICKET.HD_QUEUE_ID = 1)
    AND (HD_CATEGORY.NAME LIKE 'Software%')
  • i tried the entire script and both separately....and no results
    • Did you adjust the queue ID value and category for your environment? I just ran the query on my appliance using queue 2 and it worked (that queue has a Software category, so I left it).
      • 'General Queries' is the label for my queue and i do have a category labelled 'Software'.

        i used this:

        SELECT HD_TICKET.ID,
        HD_CATEGORY.NAME AS CATEGORY,
        Q.NAME AS QUEUE_NAME
        FROM HD_TICKET
        JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
        JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID
        WHERE (HD_TICKET.HD_QUEUE_ID LIKE 'General%')
        AND (HD_CATEGORY.NAME LIKE 'Software%')

        UNION ALL

        SELECT HD_ARCHIVE_TICKET.ID,
        HD_CATEGORY.NAME AS CATEGORY,
        Q.NAME AS QUEUE_NAME
        FROM HD_ARCHIVE_TICKET
        JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_ARCHIVE_TICKET.HD_CATEGORY_ID)
        JOIN HD_QUEUE Q ON Q.ID = HD_ARCHIVE_TICKET.HD_QUEUE_ID
        WHERE (HD_ARCHIVE_TICKET.HD_QUEUE_ID LIKE 'General%')
        AND (HD_CATEGORY.NAME LIKE 'Software%')
      • If you want to use the queue name, then you need to change HD_TICKET.HD_QUEUE_ID LIKE 'General%' and HD_ARCHIVE_TICKET.HD_QUEUE_ID LIKE 'General%' to Q.NAME like 'General%', the query is expecting an ID, which is a number.

        You also need to change the HD_CATEGORY.NAME LIKE 'Software%' to match the category that you want your report to reflect. Make sure you change it in both places.
      • this worked thank you, ill try to amend the other columns i need on the report
Please log in to comment

There are no answers at this time
Answer this question or Comment on this question for clarity

Answers

Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share