My boss asked me to figure out a report which gives Ticket Number, Title, Due Date, Status, Queue, and Ticket Owner. He wants the report to give all tickets which have a due date prior to the next week.  If the report is run on the 7th of the month, it should give all ticket which are not closed, and have a due date before the 14th of the month.  I borrowed some SQL from here: http://www.itninja.com/question/report-showing-tickets-over-x-number-of-days and after modifications.  After modifying it, I can either get all tickets, but no ticket owner, or tickets with owner, but not all tickets.  

SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.DUE_DATE,
HD_STATUS.`NAME`,
-- `USER`.FULL_NAME,
HD_QUEUE.NAME as QUEUE 
FROM HD_TICKET
INNER JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID = HD_STATUS.ID
-- INNER JOIN USER ON HD_TICKET.OWNER_ID = USER.ID
INNER JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID 
WHERE
STATE NOT IN ('closed') 
AND HD_TICKET.DUE_DATE < DATE_SUB(NOW(),INTERVAL -7 DAY)
ORDER BY DUE_DATE, ID

The report works, except with the highlighted lines commented out the ticket owner is not listed.  If I add the highlighted lines back, I do not get any unassigned tickets in the report.  

Thanks
 
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
If you use a left join instead of an inner join the query will return you unassigned tickets as well, it will return an empty username for unassigned tickets:


SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.DUE_DATE,
HD_STATUS.`NAME`,
`USER`.FULL_NAME,
HD_QUEUE.NAME as QUEUE 
FROM HD_TICKET
INNER JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID = HD_STATUS.ID
LEFT JOIN USER ON HD_TICKET.OWNER_ID = USER.ID
INNER JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID 
WHERE
STATE NOT IN ('closed') 
AND HD_TICKET.DUE_DATE < DATE_SUB(NOW(),INTERVAL -7 DAY)
ORDER BY DUE_DATE, ID
Answered 05/05/2016 by: UntchV
Yellow Belt

  • Excellent Work UntchV! Works exactly as I need it to. I've gotta' get better at my "joins". I really appreciate the help. Thank you!
Please log in to comment

Answers

Answer this question or Comment on this question for clarity