Report Query not pulling in all data
I have a saved Reporting query that pulls in all service desk tickets from specific dates that is not pulling in all my tickets from the dates I have requested. 09/19/2023 - 01/09/2024. Can someone look at this and see what is wrong?
Thanks!
SELECT HD_TICKET.ID, S.FULL_NAME AS SUBMITTER_NAME,
HD_TICKET.TIME_CLOSED,
HD_TICKET.TIME_OPENED, HD_TICKET.TITLE, O.FULL_NAME AS OWNER_NAME, GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED, HD_TICKET.APPROVAL, AP.FULL_NAME, HD_STATUS.NAME AS STATUS_NAME 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) LEFT JOIN USER AP ON (AP.ID = HD_TICKET.APPROVER_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 > '2023-09-19 12:00:01'))
AND ((HD_TICKET.TIME_OPENED < '2024-01-09 23:59:59'))
GROUP BY HD_TICKET.ID ORDER BY ID
Answers (1)
The first issue is in the "where" clause, the Column 'ID' in field list is ambiguous. Change to "Order by HD_TICKET.ID ASC"
I suspect the missing tickets may have TIME_OPENED with a value of '0000-00-00 00:00:00' /null.
You may need to add a check to include HD_TICKET.TIME_STALLED for the particular date range.
Does the following SQL pick them up in the report?
SELECT HD_TICKET.ID,
S.FULL_NAME AS SUBMITTER_NAME,
HD_TICKET.TIME_CLOSED,
HD_TICKET.TIME_OPENED,
HD_TICKET.TIME_STALLED,
HD_TICKET.TITLE,
O.FULL_NAME AS OWNER_NAME,
GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED,
HD_TICKET.APPROVAL,
AP.FULL_NAME,
HD_STATUS.NAME AS STATUS_NAME
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
LEFT JOIN USER AP ON AP.ID = HD_TICKET.APPROVER_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 > '2023-09-19 12:00:01'
AND HD_TICKET.TIME_OPENED < '2024-01-09 23:59:59' )
OR (HD_TICKET.TIME_STALLED > '2023-09-19 12:00:01'
AND HD_TICKET.TIME_STALLED < '2024-01-09 23:59:59' )
GROUP BY HD_TICKET.ID
ORDER BY HD_TICKET.ID
