/build/static/layout/Breadcrumb_cap_w.png
11/01/2018 135 views

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

Answer Summary:
2 Comments   [ + ] Show comments

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,
  • Thank you for the update. This addressed the query problem. Thank You very much!
    • Please remember to mark the question as answered.

Answer Chosen by the Author

0
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

Answered 11/05/2018 by: chucksteel
Red Belt

All Answers

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


Answered 11/02/2018 by: chucksteel
Red Belt