/build/static/layout/Breadcrumb_cap_w.png

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



0 Comments   [ + ] Show comments

Answers (1)

Posted by: KevinG 1 month ago
Red Belt
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


Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

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