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;

 

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

3

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
Answered 10/31/2012 by: jverbosk
Red Belt

  • 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.
Please log in to comment
Answer this question or Comment on this question for clarity