Hello,

I am trying to modify this current SQL code so it simply displays the # of closed tickets by owner. similar to the below format below:

1. Joe Smith 250

2. John Smith 485

 

Here is the code below:

select HD_TICKET.ID, 

       HD_TICKET.TITLE ,

       HD_TICKET.DUE_DATE ,

       DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') as TIME_CLOSED,

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

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

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

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

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

              '%kh %im')) AS TIME_TO_CLOSE,

       HD_PRIORITY.NAME as PRIORITY, 

       HD_CATEGORY.NAME as CATEGORY, 

       HD_STATUS.NAME as STATUS, 

       HD_IMPACT.NAME as IMPACT,

       MACHINE.NAME as MACHINE_NAME,

       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 = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 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
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share