Scripting

ok, so without getting in to ALOT of long winded explanation on what I have setup...

Let just say, that there is 3 tickets that are created (they are siblings)

ticket 1 has "User Accounts" in the title

ticket 2 has "Phone Ext" in the title

ticket 3 has "E-mail Migration" in the title

Now, a rule checks and if certain criteria is in the summary, then the sibling ticket with "Phone Ext" in the title is automatically closed

Then when the ticket with "User Accounts" in the title is set to status of "Waiting for Email Migration" ticket 2 and 3 are moved to another queue

Here is the part I need fixed... I only want ticket 2 with "phone Ext" in the title to be moved IF it is NOT CLOSED...

I am not sure what to add to this SQL

Here is the select (selects the "User Accounts" in title ticket and status set to "Waiting for Email Migration"

select

  distinct HD_TICKET.ID

from

  HD_TICKET

  join HD_TICKET_CHANGE c on HD_TICKET.ID = c.HD_TICKET_ID and c.ID = <CHANGE_ID>

  left join HD_TICKET_CHANGE_FIELD cf on c.ID = cf.HD_TICKET_CHANGE_ID

  join HD_STATUS S on HD_TICKET.HD_STATUS_ID = S.ID

  join HD_SERVICE_TICKET st on HD_TICKET.SERVICE_TICKET_ID = st.ID

  join HD_SERVICE serv on st.HD_SERVICE_ID = serv.ID

  ,(select S.`VALUE` ticket_label from SETTINGS S where S.`NAME` = "HD_TICKET_LABEL") settings

where

  HD_TICKET.PARENT_ID > 0 and !HD_TICKET.IS_PARENT # Child Ticket

  and serv.NAME = "Main Employee Process" # of process "Main Employee Process"

  and HD_TICKET.TITLE like "%User Accounts%" # title starts with "%User Accounts%"

  and S.NAME = "Waiting for Email Migration" # on ticket status...

  and (

    c.DESCRIPTION like concat("%", settings.ticket_label, " Created%")

    or ifnull(cf.FIELD_CHANGED, "") = "STATUS_NAME"

  ) # ...on ticket close

group by

  HD_TICKET.ID

update

  HD_TICKET

  join HD_TICKET USER_ACCOUNT_SIBLING on HD_TICKET.PARENT_ID = USER_ACCOUNT_SIBLING.PARENT_ID

    and USER_ACCOUNT_SIBLING.ID != HD_TICKET.ID

    and USER_ACCOUNT_SIBLING.PARENT_ID > 0

    and USER_ACCOUNT_SIBLING.TITLE like "%Phone Ext%"

   join HD_STATUS SIBLING_STATUS on USER_ACCOUNT_SIBLING.HD_QUEUE_ID = SIBLING_STATUS.HD_QUEUE_ID

SET 

   USER_ACCOUNT_SIBLING.HD_STATUS_ID = SIBLING_STATUS.ID

  ,USER_ACCOUNT_SIBLING.TIME_OPENED  = if(SIBLING_STATUS.STATE = 'opened',  now(), USER_ACCOUNT_SIBLING.TIME_OPENED)

  ,USER_ACCOUNT_SIBLING.TIME_CLOSED  = if(SIBLING_STATUS.STATE = 'closed',  now(), USER_ACCOUNT_SIBLING.TIME_CLOSED)

  ,USER_ACCOUNT_SIBLING.TIME_STALLED = if(SIBLING_STATUS.STATE = 'stalled', now(), USER_ACCOUNT_SIBLING.TIME_STALLED)

  ,USER_ACCOUNT_SIBLING.SATISFACTION_RATING =  if(SIBLING_STATUS.STATE = 'closed', null, USER_ACCOUNT_SIBLING.SATISFACTION_RATING)

  ,USER_ACCOUNT_SIBLING.SATISFACTION_COMMENT = if(SIBLING_STATUS.STATE = 'closed', null, USER_ACCOUNT_SIBLING.SATISFACTION_COMMENT)

  ,USER_ACCOUNT_SIBLING.HD_QUEUE_ID = 19

where

  HD_TICKET.ID = <TICKET_IDS>

and SIBLING_STATUS.NAME = "NEW"

Any suggestions are welcome

thanks

J