/build/static/layout/Breadcrumb_cap_w.png

K1000 report: Working hours per cathegory per month

A simple report to control how many hours the people was working on tickets per cathegory in the last month. The most interesting thing is the instruction to take the tickets from the last month (we are always gonna make reports at the beggining of one month to control the last one) and the sum of working hours.

select
HD_CATEGORY.NAME as 'Categoria',

SUM(format(time_to_sec(timediff(HD_WORK.STOP,HD_WORK.START))/ 3600.0 + HD_WORK.ADJUSTMENT_HOURS,2)) AS 'Horas trabalhadas'


from HD_TICKET
     INNER JOIN HD_CATEGORY ON (HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID)
     INNER JOIN HD_WORK ON (HD_TICKET.ID = HD_WORK.HD_TICKET_ID)
INNER JOIN USER ON (HD_TICKET.SUBMITTER_ID=USER.ID),
     (SELECT ADDDATE(LAST_DAY(SUBDATE(LAST_DAY(SUBDATE(CURDATE(), INTERVAL 1 MONTH)),INTERVAL 1 MONTH)),1) PrimeiroDia,
           LAST_DAY(SUBDATE(CURDATE(), INTERVAL 1 MONTH)) UltimoDia from dual) T
           
WHERE
HD_TICKET.TIME_CLOSED between T.PrimeiroDia and T.UltimoDia

     
GROUP by
HD_CATEGORY.NAME 
Posted 10 years ago 2113 views

Comments

This post is locked
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ