Initial Response Time report - need to exclude tickets originating from another queue
I have a working query here -
SELECT HD_TICKET.ID as TicketID,
HD_TICKET.CREATED as CreatedDateTime,
HD_TICKET_CHANGE.TIMESTAMP as 'ResponseDateTime',
UPDATER.FULL_NAME as 'Status Changed By',
timestampdiff(MINUTE,HD_TICKET.CREATED, HD_TICKET_CHANGE.TIMESTAMP)
FROM HD_TICKET
JOIN HD_TICKET_CHANGE
JOIN USER UPDATER on UPDATER.ID = HD_TICKET_CHANGE.USER_ID AND HD_TICKET_CHANGE.USER_ID = HD_TICKET.OWNER_ID
JOIN HD_TICKET_CHANGE_FIELD on HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID = HD_TICKET_CHANGE.ID
JOIN USER_LABEL_JT on HD_TICKET.OWNER_ID = USER_LABEL_JT.USER_ID
JOIN LABEL L on L.ID = USER_LABEL_JT.LABEL_ID
WHERE HD_TICKET.HD_QUEUE_ID=1
AND HD_TICKET.CREATED > '2012-08-31 23:56:00'
AND DAYOFWEEK(HD_TICKET_CHANGE.TIMESTAMP) BETWEEN 1 AND 6
AND HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
AND USER_LABEL_JT.LABEL_ID in (97,100,106,107,108,109,110,126,228,353,446)
AND HD_TICKET_CHANGE_FIELD.FIELD_CHANGED='STATUS_NAME'
AND HD_TICKET_CHANGE_FIELD.BEFORE_VALUE='New'
ORDER BY UPDATER.FULL_NAME
My challenge is finding a way to exclude tickets that have been in another queue before landing in Queue 1. I don't find anything in the change tables to key in on.
Answers (1)
We can queue off of the HD_TICKET_CHANGE table to see if we have been moved. We can do that by adding an exists statement in the predicate (where) of your statement like this.
not exists (select 1 from HD_TICKET_CHANGE HTC WHERE HTC.HD_TICKET_ID = HD_TICKET.ID AND DESCRIPTION LIKE '%Changed ticket Queue%')
If you look at a ticket you've moved you'll see bulleted text marking the change, these bullets are stored in the Description field of HD_TICKET_CHANGE. So whenever you look for text like that in the future you can see where to dig at.
So at the end of the day your code should look like the following.....
SELECT HD_TICKET.ID as TicketID, HD_TICKET.CREATED as CreatedDateTime, HD_TICKET_CHANGE.TIMESTAMP as 'ResponseDateTime', UPDATER.FULL_NAME as 'Status Changed By', timestampdiff(MINUTE,HD_TICKET.CREATED, HD_TICKET_CHANGE.TIMESTAMP) FROM HD_TICKET JOIN HD_TICKET_CHANGE JOIN USER UPDATER on UPDATER.ID = HD_TICKET_CHANGE.USER_ID AND HD_TICKET_CHANGE.USER_ID = HD_TICKET.OWNER_ID JOIN HD_TICKET_CHANGE_FIELD on HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID = HD_TICKET_CHANGE.ID JOIN USER_LABEL_JT on HD_TICKET.OWNER_ID = USER_LABEL_JT.USER_ID JOIN LABEL L on L.ID = USER_LABEL_JT.LABEL_ID WHERE HD_TICKET.HD_QUEUE_ID=1 AND HD_TICKET.CREATED > '2012-08-31 23:56:00' AND DAYOFWEEK(HD_TICKET_CHANGE.TIMESTAMP) BETWEEN 1 AND 6 AND HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID AND USER_LABEL_JT.LABEL_ID in (97,100,106,107,108,109,110,126,228,353,446) AND HD_TICKET_CHANGE_FIELD.FIELD_CHANGED='STATUS_NAME' AND HD_TICKET_CHANGE_FIELD.BEFORE_VALUE='New' AND not exists (select 1 from HD_TICKET_CHANGE HTC WHERE HTC.HD_TICKET_ID = HD_TICKET.ID AND DESCRIPTION LIKE '%Changed ticket Queue%') ORDER BY UPDATER.FULL_NAME
Comments:
-
Thank you very very much, it works as desired. I did followup a little on your addition as I need to understand the Select 1 and what it was doing.
Thanks again
JohnM - jmarotto 12 years ago -
OK, so Select 1..... is part of the not exists() in the where clause. So when using EXISTS() it will return true when just 1 row of data is returned from the subquery within. I use 1 because it is a small amount of data, you can use * but that will technically return more data to the query then needed to pass/fail.
It's a SQL coding practice i picked up years ago working with constrained resources on databases. - CraigT 12 years ago
