Hi,

The SQL code below creates child tickets when a new ticket is created in the queue but does not create child tickets if a ticket was moved from a different queue. Could someone assist in modifying the code below so child tickets are created when ticket is moved from a different queue or created in the same queue?

select
  HD_TICKET.ID
  ,SUBSTRING_INDEX(SUBSTRING_INDEX(HD_TICKET.CUSTOM_FIELD_VALUE7, ',', n.n), ',', -1) as 'it_need'
  ,concat(HD_TICKET.CUSTOM_FIELD_VALUE0, ", ", HD_TICKET.CUSTOM_FIELD_VALUE1, ", ", HD_TICKET.CUSTOM_FIELD_VALUE2) as employee_name
  ,HD_TICKET.CUSTOM_FIELD_VALUE4 as supervisor_name
  ,HD_TICKET.CUSTOM_FIELD_VALUE5 as supervisor_phone
  ,ifnull(SUBMITTER.USER_NAME, "Unassigned") as submitter_name
  ,CAT.NAME as category
  ,case SUBSTRING_INDEX(SUBSTRING_INDEX(HD_TICKET.CUSTOM_FIELD_VALUE7, ',', n.n), ',', -1)
    when "Hardware:Tablet" then "Hardware Group"
    when "Hardware:1 monitor" then "Hardware Group"
    when "Hardware:2 monitors" then "Hardware Group"
    when "Hardware:Oversize Monitor" then "Hardware Group"
    when "Hardware:Network Copier/Printer" then "Hardware Group"
    when "Hardware:Network Scanner" then "Hardware Group"
    when "Hardware:Desk Printer" then "Hardware Group"
    when "Network" then "Network Group"
    when "Other(s)" then "Other Group"
    when "Printer/Copier/Scanner" then "Printers Copiers Scanners Group"
    end as owner_name
  ,"tech.support" as EMAILCOLUMN
from
  HD_TICKET
  join HD_TICKET_CHANGE C on HD_TICKET.ID = HD_TICKET_ID and C.ID = <CHANGE_ID>
  cross join (select a.N + b.N * 10 + 1 n from (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a, (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) b order by n) n
  left join USER SUBMITTER on HD_TICKET.SUBMITTER_ID = SUBMITTER.ID
  join HD_CATEGORY CAT on HD_TICKET.HD_CATEGORY_ID = CAT.ID
where
  C.DESCRIPTION like '%CREATED%'
  and n.n <= 1 + (length(HD_TICKET.CUSTOM_FIELD_VALUE7) - length(REPLACE(HD_TICKET.CUSTOM_FIELD_VALUE7, ',', '')))
  and HD_TICKET.CUSTOM_FIELD_VALUE7 != ""


Thanks,
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
This rule will only match tickets on creation because of this line:
C.DESCRIPTION like '%CREATED%'
You would need to change that to something like:
(C.DESCRIPTION like "%Created%" or C.DESCRIPTION like "%Changed ticket Queue%")

Answered 12/07/2017 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity