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:
Please log in to answer
Posted by:
nshah
8 years ago
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 8 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 8 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 8 years ago
Posted by:
nshah
8 years ago
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 8 years ago
