I am currently trying to test the solution found here: http://www.itninja.com/question/auto-assign-tickets-in-a-round-robin

 

I have copied the sql statements and added them to a rule for a queue since as far as I can tell they do not need modified. When I set this rule to run "On Ticket Save" I get a confirmation email from the select query and it returns an ID that corespond to the correct ticket number but it does not get assigned to anyone. If I manually enter the values into the select and update statement and manually run the rule it correctly assigns an owner. I am not sure what else I may be missing here. I appreciate any help with this.

Here is the select and update statement I am using:

Select:

select * from

  HD_TICKET

  JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID = <CHANGE_ID>

 WHERE

  C.DESCRIPTION LIKE 'Ticket Created%'  /* new ticket */

  and ( HD_TICKET.OWNER_ID=0 OR HD_TICKET.OWNER_ID IS NULL)

Update:

UPDATE HD_TICKET

set HD_TICKET.OWNER_ID = (select BATTERUP.ID from  (select O.ID 

from

  HD_TICKET T

  JOIN HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID

  JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.HD_QUEUE_ID=Q.ID

  JOIN USER_LABEL_JT UL ON UL.LABEL_ID=QOL.LABEL_ID

  JOIN USER O ON O.ID=UL.USER_ID

  LEFT JOIN

      (select max(CREATED) MAXC, OWNER_ID, HD_QUEUE_ID from HD_TICKET WHERE  CREATED > CURDATE() GROUP BY OWNER_ID, HD_QUEUE_ID) T2

              ON    O.ID=T2.OWNER_ID and Q.ID=T2.HD_QUEUE_ID

    LEFT JOIN (select USER_ID from USER_LABEL_JT UL2 JOIN LABEL OUTLABEL

              ON OUTLABEL.ID=UL2.LABEL_ID and OUTLABEL.NAME='Out of office') OOO ON O.ID=OOO.USER_ID

where

  O.CUSTOM_1<> 'out'

  and OOO.USER_ID IS NULL

  and T.OWNER_ID=0

  and T.ID=<TICKET_IDS>

ORDER BY MAXC ASC, RAND()

LIMIT 1 )  BATTERUP)

WHERE HD_TICKET.ID =<TICKET_IDS>

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

I had to update the Select statement to instead be:

select HD_TICKET.ID from

  HD_TICKET

  JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID = <CHANGE_ID>

 WHERE

  C.DESCRIPTION LIKE 'Ticket Created%'  /* new ticket */

  and ( HD_TICKET.OWNER_ID=0 OR HD_TICKET.OWNER_ID IS NULL)

 

 

Now though I end up multiple staff always "tying" for the next ticket and it is chosen randomly. If say I have 5 tickets in 30 minutes, in the "batterup" column they all show as being number 1. Initally the all show as "NULL" and then the MAXC value changes for the first ticket they get but after that they keep tying. Any thoughts? Thanks!

Answered 07/09/2012 by: AndrewMcCabe
White Belt

Please log in to comment
Answer this question or Comment on this question for clarity