/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