I have a Report Request - Tickets with a due date which is a week from the time the report is run.
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
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
UntchV
7 years ago
Top Answer
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
Comments:
-
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! - eschmidt2050 7 years ago
