I am trying to create a report that will show me the below information, but am not too familiar with SQL and keep getting errors.  I have tried taking two reports that have things I want and putting them together but just can't get it to show up correctly how I need it.  HELP PLEASE!!!

Ticket ID, Status Date, Title, Notes added to ticket, Time worked on ticket

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

Most of these information is pretty simply. The problem are the notes, cause they can be find in the HD_WORK table and its an 1:n relation, which means one ticket can have multiple work entries. but you can try this script to get a list of all tickets with the related columns.

select t.ID, t.TITLE, s.NAME, t.CREATED, t.MODIFIED, TIMEDIFF(t.MODIFIED, t.CREATED) as TimeDiff,
      (select count(*) from HD_WORK as w where w.HD_TICKET_ID = t.ID) as CountNotes
 from HD_TICKET as t, HD_QUEUE as q, HD_STATUS as s
where t.HD_QUEUE_ID = q.ID
  and t.HD_STATUS_ID = s.ID
  and q.NAME = 'Default Queue'
order by s.NAME, t.ID
Answered 12/05/2013 by: aragorn.2003
Red Belt

  • PS. I got only the tickets from the "Default Queue". See where condition "and q.NAME = 'Default Queue'"
Please log in to comment
Answer this question or Comment on this question for clarity