/build/static/layout/Breadcrumb_cap_w.png
07/25/2019 229 views

There is a field in the master table called "TicketSource"

How is this set.

I thought it might identify tickets that are entered through the console/application and those that come from emails (we are wanting to understand the split of the number of tickets that come through email)

Have I misinterpreted this ?




0 Comments   [ + ] Show comments

Comments


All Answers

1

There isn't a TicketSource column in the HD_TICKET table, so I'm not sure what "master table" you are referencing.

That being said, the HD_TICKET_CHANGE table contains a VIA_EMAIL column which is populated with the email address that changed the ticket if the change was generated via email. Knowing that, we can look at the first "change" on a ticket to see if it was created via an email or the portal.

-- Report on number of tickets opened per month
-- Includes columns to indicate number opened via email and not (presumes opened via portal)
SELECT concat(month(CREATED), "/", year(CREATED)) as "Month/Year",
COUNT(CASE
WHEN HD_TICKET_CHANGE.VIA_EMAIL = "" THEN 1
END) AS PORTAL,
COUNT(CASE
WHEN HD_TICKET_CHANGE.VIA_EMAIL != "" THEN 1
END) AS EMAIL,
COUNT(HD_TICKET_CHANGE.ID) as TOTAL
FROM HD_TICKET
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID = (SELECT MIN(ID) FROM HD_TICKET_CHANGE WHERE HD_TICKET_ID = HD_TICKET.ID)
WHERE HD_TICKET.HD_QUEUE_ID = 2
GROUP BY YEAR(CREATED),MONTH(CREATED)
ORDER BY YEAR(CREATED),MONTH(CREATED)
Note that this is limited to queue ID 2. It also doesn't care who created the ticket. If you want a report that shows tickets that were created only by the submitter, then it will need some modification.


Answered 07/26/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