/build/static/layout/Breadcrumb_cap_w.png
01/09/2019 179 views

So I use the below Select SQL to send an email whenever a ticket is assigned to a technician. However, in many instances, they are assigning tickets to themselves. Is there a way to stop the email if a tech is assigning the ticket to themselves? 


SELECT 

    HD_TICKET.ID,

    HD_TICKET.ID AS TICKNUM,

    HD_TICKET.TITLE,

    DATE_FORMAT(HD_TICKET.CREATED,

            '%b %d %Y %I:%i:%s %p') AS CREATED,

    DATE_FORMAT(HD_TICKET.MODIFIED,

            '%b %d %Y %I:%i:%s %p') AS MODIFIED,

    C.COMMENT,

    C.DESCRIPTION,

    GROUP_CONCAT(CONCAT('----- Change by ',

                UPDATER.EMAIL,

                ' at ',

                H.TIMESTAMP,

                ' -----

                ',

                H.DESCRIPTION,

                '

                ',

                H.COMMENT,

                '

                

                Please see your ticket at http://kbox/userui/ticket.php?ID=',

                H.HD_TICKET_ID,

                '

                ')

        ORDER BY H.ID DESC

        SEPARATOR '

        ') HISTORY,

    UPDATER.USER_NAME AS UPDATER_UNAME,

    UPDATER.FULL_NAME AS UPDATER_FNAME,

    UPDATER.EMAIL AS UPDATER_EMAIL,

    IF(UPDATER.FULL_NAME = '',

        UPDATER.USER_NAME,

        UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL,

    OWNER.USER_NAME AS OWNER_UNAME,

    OWNER.FULL_NAME AS OWNER_FNAME,

    OWNER.EMAIL AS OWNER_EMAIL,

    IFNULL(OWNER.USER_NAME, 'Unassigned') OWNER_USER,

    SUBMITTER.USER_NAME AS SUBMITTER_UNAME,

    SUBMITTER.FULL_NAME AS SUBMITTER_FNAME,

    SUBMITTER.EMAIL AS SUBMITTER_EMAIL,

    P.NAME AS PRIORITY,

    S.NAME AS STATUS,

    I.NAME AS IMPACT,

    CAT.NAME AS CATEGORY,

    HD_QUEUE.NAME AS QUEUENAME

FROM

    HD_TICKET

        JOIN

    HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID

        AND C.ID = <CHANGE_ID>

        JOIN

    HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID

        JOIN

    HD_PRIORITY P ON P.ID = HD_PRIORITY_ID

        JOIN

    HD_STATUS S ON S.ID = HD_STATUS_ID

        JOIN

    HD_IMPACT I ON I.ID = HD_IMPACT_ID

        JOIN

    HD_CATEGORY CAT ON CAT.ID = HD_CATEGORY_ID

        LEFT JOIN

    USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID

        LEFT JOIN

    USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID

        LEFT JOIN

    USER UPDATER ON UPDATER.ID = C.USER_ID

        JOIN

    HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID

WHERE

    (C.DESCRIPTION LIKE '%Changed ticket Owner%')

        and OWNER.USER_NAME is not null

GROUP BY OLIST.EMAIL

HAVING 1 = 1

0 Comments   [ + ] Show comments

Comments


All Answers

0

In the where clause add:

AND UPDATER.ID != OWNER.ID


Answered 01/10/2019 by: chucksteel
Red Belt

Don't be a Stranger!

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

Sign up! or login

Share