/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


I need help writing a SQL report to show all tickets INCLUDING any tickets in the archive.

05/29/2014 3425 views

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.

Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

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