/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 5 years ago
Red Belt
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).


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

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ