SQL custom ticket rule * PLEASE HELP *
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"
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
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...
c.DESCRIPTION like concat("%", settings.ticket_label, " Created%")
or ifnull(cf.FIELD_CHANGED, "") = "STATUS_NAME"
) # ...on ticket close
Then this is the Update SQL: right now it moves the sibling if it is closed or not... (I need to not move it is if it CLOSED or NOT set it to NEW if it is already closed
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
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
HD_TICKET.ID = <TICKET_IDS>
and SIBLING_STATUS.NAME = "NEW"
Any suggestions are welcome
Having a bit of trouble following you on this (probably my fault, and I dont have a ton of time to try and process in my little brain).
Just an overall suggestion for you to consider.. have you considered a completely separate rule? If this rule as is is accomplishing one task but you are trying to add another create another and if makes logical sense you could order the rule appropriately.