Need help with K1000 Helpdesk/ServiceDesk query to show all comments AND/OR Work Notes for a given ticket and group and sort by Ticket_ID and maybe even on another report then sort by the Person entering the notes/comments.... depends on how im wanting to look at it.

Im basically trying to be able to create a report that will show me what was being done for a given time frame for all my techs.  The canned reports seem to only show Work Notes and dont include Comments and we enter both.  I would think this would be a pretty common request, but I cannot find another post on it.

Any help would be greatly appreciated.

I know comments come from the HD_Ticket_Change table and Work Notes from the HD_Work table, but I need to get all from each and group and sort probably by Ticket_ID, then date/time (and I know HD_Work uses either START field or MODIFIED field and the HD_Ticket_Change would use the TIMESTAMP field, so I dont know how to handle that either).

Thanks

1 Comment   [ + ] Show Comment

Comments

  • This gets all the notes in there (obviously dont have the main ticket tables and data and user data in here yet), but is this headed in right direction or is there a better way to accomplish this sort of thing?

    (SELECT W.HD_Ticket_ID, W.User_ID, W.Note as WNote, W.Modified as D
    FROM HD_WORK as W
    WHERE W.HD_Ticket_ID=0993
    AND W.Voided_By IS NULL)
    UNION
    (SELECT C.HD_Ticket_ID, C.User_ID, C.Description, C.Timestamp as D
    FROM HD_TICKET_CHANGE C
    WHERE HD_Ticket_ID = 0993)
    ORDER BY D asc;
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity