/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Problem displaying "Queue" Column in Service Desk Report for Archived tickets using a SQL query.

03/21/2018 492 views
Great community here, I am happy join it.  I need some help.  I am having problems displaying  "Queue" Column in Service Desk Report for Archived tickets using a SQL query.  I tried adding  Q.NAME AS QUEUE_NAME  as well as   Q_ARCHIVE.NAME AS QUEUE_NAME  to the  INITIAL SELECT  Line but it did not work.
It is giving me the error listed on the bottom.  I feel like am close.  This thread got me close but it does not mention Queue:

Archive Ticket Report Query:



SELECT HD_ARCHIVE_TICKET.ID, HD_ARCHIVE_TICKET.CREATED, HD_CATEGORY_NAME AS CATEGORY, HD_ARCHIVE_TICKET.TITLE  FROM HD_ARCHIVE_TICKET  WHERE (HD_ARCHIVE_TICKET.HD_QUEUE_ID > 0) AND (((  date(HD_ARCHIVE_TICKET.CREATED) >= date_sub(date_sub(curdate(), interval dayofmonth(curdate())-1 day), interval 4  month)  and date(HD_ARCHIVE_TICKET.CREATED) < date_sub(curdate(), interval dayofmonth(curdate())-1 day) ) ))  ORDER BY ID
Error:
mysqli error: [1054: Unknown column 'Q.NAME' in 'field list'] in EXECUTE( "SELECT HD_ARCHIVE_TICKET.ID, HD_ARCHIVE_TICKET.CREATED, Q.NAME AS QUEUE_NAME, HD_CATEGORY_NAME AS CATEGORY, HD_ARCHIVE_TICKET.TITLE FROM HD_ARCHIVE_TICKET WHERE (HD_ARCHIVE_TICKET.HD_QUEUE_ID > 0) AND ((( date(HD_ARCHIVE_TICKET.CREATED) >= date_sub(date_sub(curdate(), interval dayofmonth(curdate())-1 day), interval 4 month) and date(HD_ARCHIVE_TICKET.CREATED) < date_sub(curdate(), interval dayofmonth(curdate())-1 day) ) )) ORDER BY ID LIMIT 0")
Let me know if you can help,
-Paul
Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • That worked perfect you are amazing!

Answer Chosen by the Author

2
You need to add a join to the HD_QUEUE table to get the queue's name:
SELECT HD_ARCHIVE_TICKET.ID, HD_ARCHIVE_TICKET.CREATED, HD_CATEGORY_NAME AS CATEGORY, HD_ARCHIVE_TICKET.TITLE, HD_QUEUE.NAME as QUEUE_NAME
FROM HD_ARCHIVE_TICKET  
JOIN HD_QUEUE on HD_QUEUE.ID = HD_ARCHIVE_TICKET.HD_QUEUE_ID
WHERE (HD_ARCHIVE_TICKET.HD_QUEUE_ID > 0) AND (((  date(HD_ARCHIVE_TICKET.CREATED) >= date_sub(date_sub(curdate(), interval dayofmonth(curdate())-1 day), interval 4  month)  and date(HD_ARCHIVE_TICKET.CREATED) < date_sub(curdate(), interval dayofmonth(curdate())-1 day) ) ))  ORDER BY ID


Answered 03/22/2018 by: chucksteel
Red Belt

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