Problem displaying "Queue" Column in Service Desk Report for Archived tickets using a SQL query.
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 |
| 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") |
-Paul
1 Comment
[ + ] Show comment
-
That worked perfect you are amazing! - southwestre 6 years ago
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
6 years ago
Top Answer
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
