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