I currently have a report (deault) that shows tickets opened within the last day, by owner.  Would like to edit it to show the time worked (null if not added, actually value if added) for each ticket. I'm not a report writer, so could use some help. Here is the SQL for the report as it is today:

select HD_TICKET.ID, 

       HD_TICKET.TITLE,

       HD_TICKET.DUE_DATE,

       CONCAT(IF(TIME_TO_SEC(NOW()) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),

              TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED),

              TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ', 

              DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",

              SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),

              '%kh %im')) AS TIME_OPEN,

       HD_PRIORITY.NAME as PRIORITY, 

       HD_CATEGORY.NAME as CATEGORY, 

       HD_STATUS.NAME as STATUS, 

 

       ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,

       (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME

from HD_TICKET

left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID

left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID

left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID

left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID

left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID

where HD_STATUS.STATE = 'opened' or HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_OPENED > DATE_SUB(NOW(), INTERVAL 1 DAY)

order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL

 
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity