/build/static/layout/Breadcrumb_cap_w.png

I have this script for top categories but we also want to include ticket open date, ticket close date, technician, and submitter.  Can someone help me with this?

SELECT SUBSTRING_INDEX(HD_CATEGORY.NAME, "::", 1) AS TopCategory, COUNT(HD_TICKET.ID) FROM ORG1.HD_TICKET

JOIN HD_CATEGORY ON HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID

WHERE HD_TICKET.HD_QUEUE_ID = 5

GROUP BY TopCategory



0 Comments   [ + ] Show comments

Comments


All Answers

1

This report shows aggregate information (count of tickets), so including open and close information won't really work.

Do you want a separate report showing individual tickets, their main category, open, closed, technician and submitter?

Answered 05/02/2019 by: chucksteel
Red Belt

1
SELECT SUBSTRING_INDEX(HD_CATEGORY.NAME, "::", 1) AS TopCategory, 
HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED,
TECHNICIAN.FULL_NAME, SUBMITTER.FULL_NAME
FROM ORG1.HD_TICKET
JOIN HD_CATEGORY ON HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
JOIN USER TECHNICIAN on TECHNICIAN.ID = HD_TICKET.OWNER_ID
JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
WHERE HD_TICKET.HD_QUEUE_ID = 5


Answered 05/03/2019 by: chucksteel
Red Belt