Missed follow-up Report
Trying to run a report that will provide me with a list of incidents that have not been followed up on within a given time period, say three days. So I wrote this report below, which provides me a list of all open incidents, but would like to filter it based on the last time the tech either emailed and cc'ed the kbox or wrote a comment indicating something related to the incident.
I know I am trying to do some type of comparison on the HD_TICKET_CHANGE table and the current date, but the MYSQL is driving me crazy. I usually work with MS SQL and some of the changes are hard to follow.
Thoughts?
SELECT (SELECT NAME FROM HD_QUEUE WHERE T.HD_QUEUE_ID = HD_QUEUE.ID) AS BUCKET,
T.ID,
DATE_FORMAT(T.CREATED, '%m-%d') AS OPEN,
DATE_FORMAT((SELECT H.TIMESTAMP FROM HD_TICKET_CHANGE H
WHERE H.HD_TICKET_ID = T.ID
AND H.USER_ID IN (SELECT USER_ID FROM USER_LABEL_JT WHERE LABEL_ID IN (523, 531, 534, 535, 536, 592))
AND H.USER_ID = T.OWNER_ID
AND H.OWNERS_ONLY <> 1
ORDER BY H.TIMESTAMP DESC
LIMIT 1), '%m-%d') AS LAS_CON, #LAST CONTACT, , LIMIT 1 SHOWS MOST CURRENT(TOP 1)
IFNULL((SELECT FULL_NAME FROM USER WHERE T.OWNER_ID = USER.ID),' Unassigned') AS OWNER,
IFNULL(T.DUE_DATE, ' ') AS DUE_DATE,
(SELECT FULL_NAME FROM USER WHERE T.SUBMITTER_ID = USER.ID) AS SUBMITTER,
T.TITLE AS TITLE,
T.CUSTOM_FIELD_VALUE2 AS INCIDENT_SUMMARY
FROM HD_TICKET T
INNER JOIN HD_CATEGORY C ON C.ID = T.HD_CATEGORY_ID
INNER JOIN HD_STATUS S ON S.ID = T.HD_STATUS_ID
WHERE (S.STATE IN ('opened', 'stalled'))
#AND DATE_FORMAT(LAS_CON, '%m-%d')
ORDER BY Bucket, T.ID;
Answers (1)
Good timing - I just wrote an inline view for another question today that does something very similar to what you want to do. Give this a shot and see if it does what you want.
Hope that helps!
John
__________________
Notes:
Just change the number in
AND DATEDIFF(LAST_ACTION.MTIMESTAMP, T.CREATED) >= #
to whatever you want to specify the days filter. Currently it's set to 3 as you specified above.
If you want to see the actual number of days a ticket has sat idle, add this column to the SELECT statements (top):
DATEDIFF(LAST_ACTION.MTIMESTAMP, T.CREATED) AS DAYS_IDLE
__________________
SELECT Q.NAME AS BUCKET, T.ID,
DATE_FORMAT(T.CREATED, '%m-%d') AS OPEN,
DATE_FORMAT(LAST_ACTION.MTIMESTAMP, '%m-%d') AS LAST_CONTACT,
IFNULL(O.FULL_NAME,' Unassigned') AS OWNER,
IFNULL(T.DUE_DATE, ' ') AS DUE_DATE,
S.FULL_NAME AS SUBMITTER, T.TITLE AS TITLE,
T.CUSTOM_FIELD_VALUE2 AS INCIDENT_SUMMARY
FROM HD_TICKET T
JOIN HD_CATEGORY C ON (C.ID = T.HD_CATEGORY_ID)
JOIN HD_STATUS ST ON (ST.ID = T.HD_STATUS_ID)
JOIN USER O ON (O.ID = T.OWNER_ID)
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
JOIN HD_QUEUE Q ON (Q.ID = T.HD_QUEUE_ID)
JOIN
(SELECT C.ID, C.HD_TICKET_ID, MAX(C.TIMESTAMP) AS MTIMESTAMP
FROM HD_TICKET_CHANGE C
JOIN HD_TICKET T ON (T.ID = C.HD_TICKET_ID)
LEFT JOIN USER U ON (U.ID = T.OWNER_ID)
WHERE C.TIMESTAMP <> 0
GROUP BY C.HD_TICKET_ID
ORDER BY C.HD_TICKET_ID, C.ID)
LAST_ACTION ON (LAST_ACTION.HD_TICKET_ID = T.ID)
WHERE ST.STATE RLIKE 'opened|stalled'
AND DATEDIFF(LAST_ACTION.MTIMESTAMP, T.CREATED) >= 3
ORDER BY BUCKET, T.ID
Comments:
-
JV, I updated your sql to AND DATEDIFF(CURRENT_DATE, LAST_ACTION.MTIMESTAMP) >= 4 and that was kind of what I was looking for, but I think I almost have what I want.
I'll post in on Tuesday or so. Appreciate your help. - AJAII 12 years ago
