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.

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

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
Answered 09/26/2012 by: CraigT
Orange Belt

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