/build/static/layout/Breadcrumb_cap_w.png

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:
Posted by: UntchV 8 years ago
Yellow Belt
0

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 8 years ago

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