/build/static/layout/Breadcrumb_cap_w.png

Report query for category and time worked

We are trying to find a query that will report on the time spent per week based on the category and tech if possible.


This is a current query we are using however it does not pull the documented work time.


select HD_TICKET.ID,
       HD_TICKET.TITLE ,
       HD_TICKET.RESOLUTION ,
              HD_TICKET.TIME_CLOSED,
       HD_CATEGORY.NAME as CATEGORY,
       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 7 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL



Below is the query we are using to report on work time documented for the past 7 days for each tech but it does not include the category.


 select
 W.STOP as DATE,
 CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET,
 U.FULL_NAME, substring(W.NOTE,1,1200) as NOTE,
  format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2) as HOURS_WORKED

from
 HD_WORK W,
 HD_TICKET T,
 USER U

where
 W.HD_TICKET_ID = T.ID
 and isnull(W.VOIDED_BY)
 and W.USER_ID = U.ID
 and W.STOP > DATE_SUB(NOW(), INTERVAL 7 DAY)

order by
 U.FULL_NAME,
 TICKET, W.STOP


2 Comments   [ + ] Show comments
  • Chuck this worked however it did not include any manually added time. We include projects within our ticketing queue, We update these tickets by manually adding work time and not using the timestampdiff. Can you include the manually added time in the sql query? Thanks in advance, - twright 5 years ago
  • Thank you for the update. This addressed the query problem. Thank You very much! - twright 5 years ago
    • Please remember to mark the question as answered. - chucksteel 5 years ago

Answers (2)

Answer Summary:
Posted by: chucksteel 5 years ago
Red Belt
0
We don't use work hours so I can't completely verify this will work, but try this:
select 
HD_QUEUE.NAME,
HD_CATEGORY.NAME as CATEGORY, 
ifnull(OWNER.FULL_NAME, 'Unassigned') as 'Owner',
COUNT(HD_TICKET.ID) as 'Tickets',
IFnull(SUM(TIMESTAMPDIFF(HOUR, W.START, W.STOP)), 0) as 'Work Hours'
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 USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID
left join HD_WORK W on W.HD_TICKET_ID = HD_TICKET.ID and W.USER_ID = OWNER.ID
JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
where HD_STATUS.STATE = 'closed' 
and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY CATEGORY, OWNER.FULL_NAME
order by HD_QUEUE.NAME, CATEGORY, OWNER.FULL_NAME


Posted by: chucksteel 5 years ago
Red Belt
0

Top Answer

This includes adjustment hours:
select 
HD_QUEUE.NAME,
HD_CATEGORY.NAME as CATEGORY, 
ifnull(OWNER.FULL_NAME, 'Unassigned') as 'Owner',
COUNT(HD_TICKET.ID) as 'Tickets',
IFnull(SUM(TIMESTAMPDIFF(HOUR, W.START, W.STOP))+SUM(W.ADJUSTMENT_HOURS), 0) as 'Work Hours'
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 USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID
left join HD_WORK W on W.HD_TICKET_ID = HD_TICKET.ID and W.USER_ID = OWNER.ID
JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
where HD_STATUS.STATE = 'closed' 
and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY CATEGORY, OWNER.FULL_NAME
order by HD_QUEUE.NAME, CATEGORY, OWNER.FULL_NAME

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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