/bundles/itninjaweb/img/Breadcrumb_cap_w.png

I'm looking for an executive report that shows all tickets that have been opened in the past 12 months. Currently I have archiving set up for tickets that have been closed over 6 months. I can only seem to report on EITHER the archive OR my service desk, but not a report that will give me a true ticket report from BOTH.

 

Thanks.

2 Comments   [ - ] Hide Comments

Comments

  • 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.
    • If both queries have the same columns (number and data type), you can union the two queries into one report. http://dev.mysql.com/doc/refman/5.6/en/union.html
  • 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.
Please log in to comment

There are no answers at this time
Answer this question or Comment on this question for clarity

Answers

Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share