I have a report that lists all open project tickets. My management team would like to include work notes and work hours in this report. From what I understand, you can only use one table in the reporting wizard and that's my dilemma. I assume that I will have to incorporate the HD_WORK table information manually and this is where I need some assistance.
The following is the SQL for my current report:
SELECT HD_TICKET.CREATED AS HD_TICKET_CREATED, HD_TICKET.DUE_DATE AS HD_TICKET_DUE_DATE, O.FULL_NAME AS OWNER_NAME, HD_STATUS.NAME AS STATUS, S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.ID AS HD_TICKET_ID, HD_TICKET.TITLE AS HD_TICKET_TITLE, HD_TICKET.CUSTOM_FIELD_VALUE0 AS HD_TICKET_CUSTOM_FIELD_VALUE0, HD_TICKET.CUSTOM_FIELD_VALUE1 AS HD_TICKET_CUSTOM_FIELD_VALUE1, HD_TICKET.CUSTOM_FIELD_VALUE2 AS HD_TICKET_CUSTOM_FIELD_VALUE2, HD_TICKET.CUSTOM_FIELD_VALUE3 AS HD_TICKET_CUSTOM_FIELD_VALUE3 FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 25) AND ( HD_STATUS.NAME NOT LIKE '%Closed%') ORDER BY HD_TICKET.DUE_DATE asc,O.FULL_NAME asc,HD_TICKET.ID asc,HD_TICKET.CUSTOM_FIELD_VALUE3 asc
How can I incorporate the work notes and work hours for each project into this report? What would be the SQL needed for this to happen? Any assistance would be greatly appreciated!
Thanks in advance.
Report SQL Assistance (HD_WORK)
so that the conversation will remain readable.