/build/static/layout/Breadcrumb_cap_w.png

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"

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"

  ) # ...on ticket close

group by

  HD_TICKET.ID



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


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"



Any suggestions are welcome


thanks


J


0 Comments   [ + ] Show comments

Answers (1)

Posted by: barchetta 2 weeks ago
Black Belt
0

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.


Comments:
  • the rule basically when ticket a is set to status of "Waiting for Migration" moves sibling ticket with "Phone Ext" in the title to other queue, the issue is I do not want to move the ticket if it is already closed (from a previous rule) but the ticket IS moving the way it is written...
    So what I wanted to try to figure out is how to say in here if sibling ticket is NOT closed move...
    only thing I can think of is adding something like
    and USER_ACCOUNT_SIBLING.HD_STATUS_ID != 199
    just under
    and USER_ACCOUNT_SIBLING.TITLE like "%Phone Ext%"
    in the update SQL

    I will have to test... - jct134 2 weeks ago
    • Sounds right to me.. - barchetta 2 weeks ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
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