Hello,

We are trying to create a report for all tickets opened under the Hardware category over the past 30 days in a specific queue.

We have run the following SQL query, but this is generating all tickets from all categories in the specific queue:


SELECT T.ID AS 'Ticket #', T.TITLE AS 'Issue', S.NAME AS 'Status' FROM HD_TICKET T
 JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
 WHERE DATEDIFF(NOW(), CREATED) < 30 AND T.HD_QUEUE_ID = 1


If anyone could provide any additional info on getting the Hardware category isolated in the report that would be great.


Thanks in advance!


Pete 

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
This worked in my system when I changed hardware to internal. Check the case sensitivity of the word hardware. Hardware or hardware. 

SELECT
HD_TICKET.CREATED,HD_CATEGORY.NAME AS CATEGORY,
HD_TICKET.TITLE,S.FULL_NAME AS SUBMITTER_NAME,
O.FULL_NAME AS OWNER_NAME 
FROM HD_TICKET
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) 
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND (HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 31 DAY) ) AND (HD_CATEGORY.NAME like '%hardware%')
ORDER BY CREATED desc,CATEGORY
Answered 04/13/2016 by: nshah
Red Belt

  • Hey, that worked! Thanks again for your help with this!
Please log in to comment

Answers

0
Give this a try.

Select
  T.ID As 'Ticket #',
  T.TITLE As 'Issue',
  S.NAME As 'Status',
  HD_CATEGORY.NAME As Category
From
  HD_TICKET T Join
  HD_STATUS S
    On T.HD_STATUS_ID = S.ID,
  HD_CATEGORY
Where
  DateDiff(Now(), T.CREATED) < 30 And
  T.HD_QUEUE_ID = 1 And
  HD_CATEGORY.NAME Like 'HARDWARE'
Answered 04/13/2016 by: nshah
Red Belt

  • Hi nshah,

    That query generated the following MySQL error:

    [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'T.ID As 'Ticket #', T.TITLE As "Issue', S.NAME As 'Status', HD_CATEGORY' at line 1] in EXECUTE( "T.ID AS 'Ticket #', T.TITLE As 'Issue', S.NAME As 'Status', HD_CATEGORY.NAME As Category From HD_TICKET T Join HD_STATUS S On T.HD_STATUS_ID = S.ID, HD_CATEGORY Where DateDiff(Now(), T.CREATED) < 30 And T.HD_QUEUE_ID =1 And HD_CATEGORY.NAME Like 'HARDWARE' LIMIT 0")
    • I'm not uber great with SQL but this gives everything under hardware, you may just have to add the queue and the 30 days to the WHERE statement

      Select
      HD_TICKET.TITLE As Issue,
      HD_CATEGORY.NAME As Category,
      HD_STATUS.NAME As Status,
      HD_TICKET.ID As Ticket
      From
      HD_CATEGORY Inner Join
      HD_TICKET T
      On HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID Inner Join
      HD_STATUS
      On HD_TICKET.HD_STATUS_ID = HD_STATUS.ID
      Where
      HD_CATEGORY.NAME = 'Hardware'
      • Hello again,

        We added the queue and 30 days to the WHERE statement and still get a MySQL error. Our SQL guru is out today so I'm just trying to figure this out myself until he's back.

        I found in another thread, the following queury, which gets us created, category, title, submitter name and owner name.

        SELECT
        HD_TICKET.CREATED,HD_CATEGORY.NAME AS CATEGORY,
        HD_TICKET.TITLE,S.FULL_NAME AS SUBMITTER_NAME,
        O.FULL_NAME AS OWNER_NAME
        FROM HD_TICKET
        JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
        LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
        LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND (HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 31 DAY) )
        ORDER BY CREATED desc,CATEGORY

        For now, we can just use filter and sorting in Excel to locate all the Hardware category tickets.

        I'll check with him tomorrow once he's back to see if he knows why we're getting those SQL errors.

        Thanks!

        Peter
Please log in to comment
Answer this question or Comment on this question for clarity

Share