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
-
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 6 years ago
-
Thank you for the update. This addressed the query problem. Thank You very much! - twright 6 years ago
-
Please remember to mark the question as answered. - chucksteel 6 years ago
Answers (2)
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
Top Answer
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
