/build/static/layout/Breadcrumb_cap_w.png

Hardware category report for the past 30 days

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 


0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: nshah 4 years ago
Red Belt
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'

Comments:
  • 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") - Peterphan 4 years ago
    • 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' - nshah 4 years ago
      • 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 - Peterphan 4 years ago
Posted by: nshah 4 years ago
Red Belt
0

Top Answer

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

Comments:
  • Hey, that worked! Thanks again for your help with this! - Peterphan 4 years ago
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ