SQL Query for Helpdesk Report
I have modified one of the canned K1000 reports to only run against a specific set of users for ticket tracking, however I'm having trouble gettting the syntax added that will allow this ticket to run like the Open tickets by owver for 7 days. I want that type of time interval for my quesry, but can't get it to work.....HELP!
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 = 'Open' OR HD_STATUS.STATE = 'Stalled'
AND OWN.FULL_NAME IN ( 'David Andreason', 'Tim Rowe','Darrell Dubois','Devon Rylski','Edward Abbott','Joe Vermeulen','Scott Legros')
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 = 'Open' OR HD_STATUS.STATE = 'Stalled'
AND OWN.FULL_NAME IN ( 'David Andreason', 'Tim Rowe','Darrell Dubois','Devon Rylski','Edward Abbott','Joe Vermeulen','Scott Legros')
ORDER BY OWNER_NAME,
HD_PRIORITY.ORDINAL,
HD_CATEGORY.ORDINAL,
HD_STATUS.ORDINAL,
HD_IMPACT.ORDINAL
0 Comments
[ + ] Show comments
Answers (1)
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 = 'Open' OR HD_STATUS.STATE = 'Stalled' AND HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY) AND OWN.FULL_NAME IN ( 'David Andreason', 'Tim Rowe', 'Darrell Dubois', 'Devon Rylski', 'Edward Abbott', 'Joe Vermeulen', 'Scott Legros' ) 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 = 'Open' OR HD_STATUS.STATE = 'Stalled' AND HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY) AND OWN.FULL_NAME IN ( 'David Andreason', 'Tim Rowe', 'Darrell Dubois', 'Devon Rylski', 'Edward Abbott', 'Joe Vermeulen', 'Scott Legros' ) 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
You can add this line to your where statement:
HD_TICKET.CREATED > DATE_SUB(NOW(),INTERVAL 7 DAY)
The full query would be something like this:
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 = 'Open'
OR HD_STATUS.STATE = 'Stalled'
AND HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
AND OWN.FULL_NAME IN ( 'David Andreason', 'Tim Rowe', 'Darrell Dubois', 'Devon Rylski',
'Edward Abbott', 'Joe Vermeulen', 'Scott Legros' )
ORDER BY OWNER_NAME,
HD_PRIORITY.ORDINAL,
HD_CATEGORY.ORDINAL,
HD_STATUS.ORDINAL,
HD_IMPACT.ORDINAL
HD_TICKET.CREATED > DATE_SUB(NOW(),INTERVAL 7 DAY)
The full query would be something like this:
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 = 'Open'
OR HD_STATUS.STATE = 'Stalled'
AND HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
AND OWN.FULL_NAME IN ( 'David Andreason', 'Tim Rowe', 'Darrell Dubois', 'Devon Rylski',
'Edward Abbott', 'Joe Vermeulen', 'Scott Legros' )
ORDER BY OWNER_NAME,
HD_PRIORITY.ORDINAL,
HD_CATEGORY.ORDINAL,
HD_STATUS.ORDINAL,
HD_IMPACT.ORDINAL
