Service Desk Report
hi guys,
I need some help with putting together a report and I am a newbie to mysql, and sql in general.
what I am trying to accomplish is a report that shows new tickets within the last 24 hours.
any ticket that came in the last 24 hours. whether its been assigned, opened, or closed.
any help would be appreciated. id be happy to provide additional info as needed.
I need some help with putting together a report and I am a newbie to mysql, and sql in general.
what I am trying to accomplish is a report that shows new tickets within the last 24 hours.
any ticket that came in the last 24 hours. whether its been assigned, opened, or closed.
any help would be appreciated. id be happy to provide additional info as needed.
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
dchristian
12 years ago
Hey ryklism,
Try this:
Try this:
SELECT HD_TICKET.CREATED,
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
WHERE HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 1 DAY)
ORDER BY OWNER_NAME,
HD_PRIORITY.ORDINAL,
HD_CATEGORY.ORDINAL,
HD_STATUS.ORDINAL,
HD_IMPACT.ORDINAL
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.
