/build/static/layout/Breadcrumb_cap_w.png
01/10/2019 218 views

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'))

0 Comments   [ + ] Show comments

Comments


All Answers

0

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).


Answered 01/11/2019 by: chucksteel
Red Belt