SQL - need to change status of a particular sibling ticket when another sibling is closed.. but not change that status if the sibling is already closed.
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:
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 "%E-mail Migration%" # title Contains "E-mail Migration"
and S.STATE = "closed" # on ticket close...
c.DESCRIPTION like concat("%", settings.ticket_label, " Created%")
or ifnull(cf.FIELD_CHANGED, "") = "STATUS_NAME"
) # ...on ticket close
UPDATE SQL CODE:
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
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)
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
Just an update..
I could not figure out how to only update the sibling status if the sibling status was NOT "closed" however I did manage to add:
and USER_ACCOUNT_SIBLING.RESOLUTION = ""
So if the sibling resolution is "" or empty, (meaning the sibling is not closed, as all closed need resolution) then the status will be changed..
I guess this works.. not quite the way I was looking for.. but still will keep the closed sibling ticket from being set back to status of "Email migrated" if they are already processed & closed