/build/static/layout/Breadcrumb_cap_w.png

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:

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




0 Comments   [ + ] Show comments

Answers (1)

Posted by: jct134 2 years ago
Senior Purple Belt
0

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



J

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ