I'm attempting to create a report that will show the total number of tickets with a specified subject (They should all be identical), as well as the name of the person who closed them. What I have below is giving me a syntax error:

SELECT O.FULL_NAME, COUNT(HD_TICKET.TIME_OPENED) FROM HD_TICKET  LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ((HD_TICKET.TIME_OPENED like '%2016%') AND (HD_TICKET_SUBJECT like %Additional request form and New Hire process (child)%)  GROUP BY O.FULL_NAME


If I remove the AND (HD_TICKET_SUBJECT like %Additional request form and New Hire process (child)%) portion, the report will spit out total tickets in 2016, closed by owner.


My goal is to get more specific and see how many of those have the specific subject I'm looking for.

1 Comment   [ + ] Show Comment

Comments

  • Have you tried to build this report using the wizard?
Please log in to comment

Answers

0
There is no table called HD_TICKET_SUBJECT. Try HD_TICKET.TITLE
Answered 08/17/2017 by: Druis
Second Degree Blue Belt

  • When I use your suggested replacement, I still have an error:

    mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%Additional request form and New Hire process (child)%) GROUP BY O.FULL_NAME LI' at line 1] in EXECUTE( "SELECT O.FULL_NAME, COUNT(HD_TICKET.TIME_OPENED) FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ((HD_TICKET.TIME_OPENED like '%2016%') AND (HD_TICKET.TITLE like %Additional request form and New Hire process (child)%) GROUP BY O.FULL_NAME LIMIT 0")
    • Tyr this:-
      SELECT O.FULL_NAME,
      COUNT(HD_TICKET.TIME_OPENED)
      FROM HD_TICKET
      LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
      WHERE (HD_TICKET.HD_QUEUE_ID = 1)
      AND
      (YEAR(HD_TICKET.CREATED) = 2017)
      AND (HD_TICKET.TITLE like '%Additional request form and New Hire process (child)%')
      GROUP BY O.FULL_NAME
      • That's some progress at least. I can save the report without errors now, but it's not retrieving any results. The ticket title I have should be exact (they're auto generated child tickets, so there shouldn't be any variation)
  • Are you sure of your queue ID? 1 is usually the default but if there are multiple Queues they will all have different numbers
    • Yes I'm positive. If I remove
      AND (HD_TICKET.TITLE like '%Additional request form and New Hire process (child)%')
      it spits out total number of tickets closed per user.
  • I'm not sure where your issue is. When I run this query with my own key text I get a return. You may benefit from using a tool like MySQL workbench or HeidiSQL here to interrogate your tables
    • I just tried again with only the word Additional as a qualifier and it worked. I'm thinking that maybe it can't handle the extra set of parenthesis
Please log in to comment
Answer this question or Comment on this question for clarity