Scripting

Because you are pulling from two different tables without a relationship between them I think the best way to accomplish this is with two reports. I messed around a little but and can't figure out a way to pull from both archived and active ticket tables. - chucksteel 10 years ago

I am trying to get a Service Desk report for the last 4 months that spans both active and archive tickets. Can anyone help me with my query?

__________My Archive Query:__________

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



__________My Active Ticket Query:__________

SELECT HD_TICKET.ID, HD_TICKET.CREATED, Q.NAME AS QUEUE_NAME, HD_CATEGORY.NAME AS CATEGORY, HD_TICKET.TITLE FROM HD_TICKET JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) WHERE (HD_TICKET.HD_QUEUE_ID > 0) AND ((( date(HD_TICKET.CREATED) >= date_sub(date_sub(curdate(), interval dayofmonth(curdate())-1 day), interval 4 month) and date(HD_TICKET.CREATED) < date_sub(curdate(), interval dayofmonth(curdate())-1 day) ) )) ORDER BY ID

_________________________________
Let me know if I should do a fresh post instead of bumping an old thread. - southwestre 6 years ago