Scripting

Here is the code I have so far

select

  distinct HD_TICKET.ID,

HD_TICKET.CUSTOM_VALUE4 AS PHONE,

HD_TICKET.CUSTOM_VALUE1 AS USERNAME,

HD_TICKET.CUSTOM_VALUE2 AS PASSWORD,

HD_TICKET.CUSTOM_VALUE3 AS EMAIL,

  case

    when HD_TICKET.SUMMARY like "%Company: company1%" then "Email1@somewhere.com"

    when HD_TICKET.SUMMARY like "%Company: company2%" then "Email2@somewhere.com"

    when HD_TICKET.SUMMARY like "%Company: company3%" then "Email3@somewhere.com"

    when HD_TICKET.SUMMARY like "%Company: company4%" then "Email4@somewhere.com"

    when HD_TICKET.SUMMARY like "%Company: company5%" then "Email5@somewhere.com"

    else "Email@somewhere.com"

  end as NEWTICKETEMAIL -- $newticketemail

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 # Parent Ticket

  and serv.NAME = "Main Employee Process" # of process "Main Employee Process"

  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