Work Report
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
Answers (1)
Please log in to answer
Posted by:
aragorn.2003
11 years ago
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
Comments:
-
PS. I got only the tickets from the "Default Queue". See where condition "and q.NAME = 'Default Queue'" - aragorn.2003 11 years ago
