/build/static/layout/Breadcrumb_cap_w.png

SQL that if ticket A is set to certain status, the sibling ticket has status set to NEW & moved to another Queue

OK, so I have this flow..


3 sibling tickets, each with distinct word in the title (1 has Phone Ext-, 1 has E-mail Migration, & the other has User-Accounts)


If the child that has User-Accounts in the title is set to status of Waiting for email migration, then I want the other 2 tickets to have the status set to "NEW" and also move them into another Queue (2 separate CTR)


I have this SQL working to move the tickets to the other Queue, however when I tried to set the status, I somehow changed all 5 of my possible status names for the Queue to "New" oops!


Here is what I have that moves the ticket to another Queue, to set the status of these tickets to "New" before moving them does anyone have an answer? (this is 1 of the CTR that I have)

SELECT:

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"

  ) # ...Waiting for Email Migration

group by

  HD_TICKET.ID



Update:

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_QUEUE_ID = 19

where

  HD_TICKET.ID = <TICKET_IDS>



Any help would be appreciated..


thanks


Jason


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: jct134 10 months ago
Purple Belt
0

Top Answer

This is what I came up with that seems to work:


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"

 
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