Scripting

So, in this process... a child ticket that has %User Accounts% in the title along with a sibling ticket that has %email migration% in the title...

When the child that has Email migration in the title is closed, it updates the status of the sibling that has User Accounts in the title.. (this works perfect...)

What we want to stop from happening.. is if the user accounts child is closed first, then the E-mail migration sibling does NOT change the status of the User Accounts sibling if it is closed after...

I hope that makes sense...

Here is what I have to the Select & Update codes:

SELECT SQL CODE:

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 "%E-mail Migration%" # title Contains "E-mail Migration"

  and S.STATE = "closed" # on ticket close...

  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 SQL CODE:

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 "%User Accounts%"

  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)

where

  HD_TICKET.ID = <TICKET_IDS>

  and SIBLING_STATUS.NAME = "E-mail Migrated"

I am not sure if the select side I can add something like

and USER_ACCOUNT_SIBLING.HD_STATUS.ID != "199"

Or something like that??

If anyone has any suggestions please let me know

thanks

J