I am trying to run a ticket report for All Ticket from a certain date range. It runs successfully but for some reason it skips a particular ticket in the range, Ticket#4284. My auditors need the report and I am not sure why it is skipping that ticket? Or can someone tell me how to modify the ticket to specifically include Ticket 4284 to this All Tickets report...where and what would I add?

SELECT HD_TICKET.CREATED, S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.ID, HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED, HD_TICKET.TITLE, O.FULL_NAME AS OWNER_NAME, HD_TICKET.APPROVAL, (LEFT(GROUP_CONCAT(DISTINCT COMMENT SEPARATOR '
'),255)) AS SHORT_COMMENT, HD_STATUS.NAME AS STATUS  FROM HD_TICKET  LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ((HD_TICKET.TIME_OPENED > '2015-10-16 23:59:59'))  GROUP BY HD_TICKET.ID ORDER BY ID, TIME_CLOSED, TITLE

2 Comments   [ + ] Show Comments

Comments

  • Try to amend the query and remove the AND ((HD_TICKET.TIME_OPENED > '2015-10-16 23:59:59')) part.
    If removing that you can see the missing tickets it means that the problem is that the tickets that you do not see do not satisfy that condition..
    Regards,
    Marco - StockTrader
    • It shows up. The date created was actually on 10/30/2015 but in the report it is blank for some reason, which is why it didn't show on my report. Can you tell me how to add this one ticket number to my original script? I need the report to begin on 10/16/15 per my audit request. How can I use that and then add to also include ticket id 4284, or can I?
      • So there is something in that ticket that does not match the date condition.
        You can do a ''dirty trick''
        Try the following:
        AND ((HD_TICKET.TIME_OPENED > '2015-10-16 23:59:59') OR HD_TICKET.ID = 4284)
        if you need to add more tickets you can use something like that:
        AND ((HD_TICKET.TIME_OPENED > '2015-10-16 23:59:59') OR HD_TICKET.ID IN (4284,...,...,..))
        Kind regards,
        Marco - StockTrader
  • Are you looking for tickets that were set to "Open" state after that time, or are you looking for tickets "Created" after that time. All tickets have the HD_TICKET.CREATED filled.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity