/build/static/layout/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   [ + ] Show 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.

There are no answers at this time