Custom Date Ticket Report
What do I have wrong that the report only pulls one ticket from 09/05/2018 instead of what I needs which is all tickets after 09/04/2018 and through the end of the 2018 year?
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)
JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
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 > '2018-09-04 23:59:59'))
AND ((HD_TICKET.TIME_OPENED < '2018-12-31 23:59:59'))
Answers (1)
Use between instead of the two greater than and less than restrictions:
AND DATE(HD_TICKET.CREATED) BETWEEN '2018-09-04' and '2018-12-31'
I generally use the CREATED column for reporting on ticket creation. The TIME_OPENED column contains the timestamp for when the ticket is placed in an open state, so this doesn't always equate to when the ticket is created. Actually, unless you create tickets with a status that has an open state, it may never equal the time the ticket was created. The default configuration for the New status is stalled, so tickets that go directly from New to Closed will not have a TIME_OPENED entry at all (it will be 0000-00-00 00:00:00).
