Report Tweak
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
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
0 Comments
[ + ] Show comments
Answers (2)
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
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
Please log in to answer
Posted by:
dchristian
12 years ago
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
Posted by:
kwilliams162
12 years ago
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
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.