The canned report for Open and Stalled tickets was a great starting point for my problem in that I needed to run the query against a specific queue which I was able to figure out. However now I have been brought a new request.....

Suppose I have two users Dave and Tim. Dave belongs only to the Desktop Support queue while Tim belings to Desktop Support, Multimedia, and Network/Phone queues. I need to modify the following so that it only runs against the two users, but provides all of Tim's tickets that match the criteria regardless of the queue they're in.

select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE,
CONCAT(IF(TIME_TO_SEC(NOW()) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED),
TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
'%kh %im')) AS TIME_OPEN,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
Join HD_QUEUE
ON HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
where HD_STATUS.STATE =( 'stalled' OR HD_STATUS.STATE = 'opened')
AND HD_QUEUE.NAME = 'Desktop Support'
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
Answer Summary:
SELECT HD_TICKET.ID, HD_QUEUE.NAME, HD_TICKET.TITLE, HD_TICKET.DUE_DATE, Concat(IF(Time_to_sec(NOW()) >= Time_to_sec(HD_TICKET.TIME_OPENED), To_days(NOW()) - To_days(HD_TICKET.TIME_OPENED), To_days(NOW()) - To_days(HD_TICKET.TIME_OPENED) - 1), 'd ', Date_format(Addtime("2000-01-01 00:00:00", Sec_to_time(Time_to_sec(NOW()) - Time_to_sec(HD_TICKET.TIME_OPENED))), '%kh %im')) AS TIME_OPEN, HD_PRIORITY.NAME AS PRIORITY, HD_CATEGORY.NAME AS CATEGORY, HD_STATUS.NAME AS STATUS, HD_IMPACT.NAME AS IMPACT, MACHINE.NAME AS MACHINE_NAME, Ifnull(OWN.FULL_NAME, ' Unassigned') AS OWNER_NAME, (SELECT FULL_NAME FROM USER WHERE HD_TICKET.SUBMITTER_ID = USER.ID) AS SUBMITTER_NAME FROM HD_TICKET LEFT JOIN HD_CATEGORY ON HD_CATEGORY_ID = HD_CATEGORY.ID LEFT JOIN HD_STATUS ON HD_STATUS_ID = HD_STATUS.ID LEFT JOIN HD_PRIORITY ON HD_PRIORITY_ID = HD_PRIORITY.ID LEFT JOIN HD_IMPACT ON HD_IMPACT_ID = HD_IMPACT.ID LEFT JOIN MACHINE ON HD_TICKET.MACHINE_ID = MACHINE.ID LEFT JOIN USER OWN ON HD_TICKET.OWNER_ID = OWN.ID JOIN HD_QUEUE ON HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID WHERE HD_STATUS.STATE = ( 'stalled' OR HD_STATUS.STATE = 'opened' ) AND OWN.FULL_NAME IN ( 'DAVE', 'TIM' ) ORDER BY OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1
Try this, just make sure you change there names towards the end of the statement.

SELECT HD_TICKET.ID,
HD_QUEUE.NAME,
HD_TICKET.TITLE,
HD_TICKET.DUE_DATE,
Concat(IF(Time_to_sec(NOW()) >= Time_to_sec(HD_TICKET.TIME_OPENED), To_days(NOW()) - To_days(HD_TICKET.TIME_OPENED), To_days(NOW()) - To_days(HD_TICKET.TIME_OPENED) - 1), 'd ', Date_format(Addtime("2000-01-01 00:00:00", Sec_to_time(Time_to_sec(NOW()) - Time_to_sec(HD_TICKET.TIME_OPENED))), '%kh %im')) AS TIME_OPEN,
HD_PRIORITY.NAME AS PRIORITY,
HD_CATEGORY.NAME AS CATEGORY,
HD_STATUS.NAME AS STATUS,
HD_IMPACT.NAME AS IMPACT,
MACHINE.NAME AS MACHINE_NAME,
Ifnull(OWN.FULL_NAME, ' Unassigned') AS OWNER_NAME,
(SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.SUBMITTER_ID = USER.ID) AS SUBMITTER_NAME
FROM HD_TICKET
LEFT JOIN HD_CATEGORY
ON HD_CATEGORY_ID = HD_CATEGORY.ID
LEFT JOIN HD_STATUS
ON HD_STATUS_ID = HD_STATUS.ID
LEFT JOIN HD_PRIORITY
ON HD_PRIORITY_ID = HD_PRIORITY.ID
LEFT JOIN HD_IMPACT
ON HD_IMPACT_ID = HD_IMPACT.ID
LEFT JOIN MACHINE
ON HD_TICKET.MACHINE_ID = MACHINE.ID
LEFT JOIN USER OWN
ON HD_TICKET.OWNER_ID = OWN.ID
JOIN HD_QUEUE
ON HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE HD_STATUS.STATE = ( 'stalled'
OR HD_STATUS.STATE = 'opened' )
AND OWN.FULL_NAME IN ( 'DAVE', 'TIM' )
ORDER BY OWNER_NAME,
HD_PRIORITY.ORDINAL,
HD_CATEGORY.ORDINAL,
HD_STATUS.ORDINAL,
HD_IMPACT.ORDINAL
Answered 03/16/2012 by: dchristian
Red Belt

Please log in to comment
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
worked perfectly David.....thx. Now the only other thing I forgot to mention was breaking this into two seperate reports where the timeline is 1 week, and the other is 1 month. Other than that, it works great
Answered 03/16/2012 by: kwilliams162
Senior Yellow Belt

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