/build/static/layout/Breadcrumb_cap_w.png
11/14/2016 1066 views
I found this great link on how to create the report
https://support.software.dell.com/k1000-systems-management-appliance/kb/111916
I want to break it down into smaller amounts of data. I would like to be able to view the report by technician's, and view it by weeks or by month.
I am not good with SQL in any way. So all of this is new to me
Any help with creating this report would be great
Also would there be a way to show subcategories 
Thank you
1 Comment   [ + ] Show comment

Comments

  • Hi cjohnson

    I created this report for the company I am working for...

    SELECT Round((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, TIME_OPENED))/3600)/(select count(id) from HD_TICKET as hdticket2 where hdticket2.OWNER_ID=hdticket1.OWNER_ID)),3) as 'MTBC(Hours)', USER.USER_NAME as Username
    FROM HD_TICKET as hdticket1
    LEFT JOIN USER ON hdticket1.OWNER_ID = USER.ID
    LEFT JOIN HD_STATUS ON hdticket1.HD_STATUS_ID = HD_STATUS.ID
    where month(hdticket1.TIME_CLOSED) = month(current_date-interval 1 month)
    and year(hdticket1.TIME_CLOSED) = year(now())
    AND STATE like '%close%'
    AND hdticket1.HD_QUEUE_ID IN (1,3,4,9)
    GROUP BY Username ASC WITH ROLLUP

    So what it basically does is to round and sum the calculated difference (in seconds) between the hd_ticket.time_closed and hd_ticket.time_opened and then divide it by 3600 to convert it to hours.

    Then I select the username for the technician and set a filter to select only tickets that were closed in the previous calendar month and from only 4 queues.

    I want the previous calendar month because we run our KPI on the first of every month.

All Answers

0
Hi,

You can try this

SELECT
(case when
TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),
INTERVAL 1 HOUR) then '0-1 hour'
when
TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL
24 HOUR) then '1-24 hours'
when
TIME_CLOSED>DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),INTERVAL
24 HOUR) then '>24 hours'
else 'error' end ) as Selection,

count(HD_TICKET.ID) as Nombre_de_ticket_des_30_derniers_jours,
CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
div 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id) mod 86400 ), '%kh %im %ss')) as Temps_de_traitement_moyen_par_ticket from HD_TICKET,
HD_STATUS
where
HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
and HD_STATUS.NAME='closed'
and TIME_CLOSED<>0
and TIME_CLOSED>'2007-01-01' /*change the start date here*/
and TIME_CLOSED<'2008-04-30' /*change the end date here*/
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
group by Selection

UNION
select 'Tous les tickets' as Selection,count(HD_TICKET.ID) as Nombre_de_ticket_des_30_derniers_jours,
CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
div 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
mod 86400 ), '%kh %im %ss')) as Temps_de_traitement_moyen_par_ticket from HD_TICKET
,HD_STATUS
where
HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
and HD_STATUS.NAME='closed'
and TIME_CLOSED<>0
and TIME_CLOSED>'2016-04-01' /*change the start date here*/
and TIME_CLOSED<'2050-12-25' /*change the end date here */
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
and DATEDIFF(NOW(), CREATED) < 30
group by Selection

I am french so my column are explain in french.

This SQL do what you want for my K1000 for the last 30 days (not the month).
Answered 11/16/2016 by: Olendis
Orange Belt