I'm looking for a report that would show me hours worked per user
I have tried several report samples that should show hours worked but they do not produce the results I'm looking for. I would like a report that would show me how many hours each tech worked per day not a list of tickets. i.e I would like to see a report that says Joe closed 5 tickets and worked 6 hours.
Answers (2)
Give this a spin - it's a mod of the "Work Report last 31 days by person" canned report:
select W.STOP as DATE, U.FULL_NAME as OWNER,COUNT(T.ID) as TOTAL_TICKETS,
sum(format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2)) as HOURS_WORKED
from HD_WORK W
join HD_TICKET T on (T.ID = W.HD_TICKET_ID)
join USER U on (U.ID = T.OWNER_ID)
where isnull(W.VOIDED_BY)
and W.STOP > DATE_SUB(NOW(), INTERVAL 31 DAY)
group by OWNER, DATE
order by DATE, OWNER
We don't keep track of hours where I work, but fortunately one of the guys did so for a handful of tickets (5 - with 2 on the same day) so it was *barely* enough to work with. There weren't any hours listed in these tickets, so I'm making a best guess that SUM should total the hours.
Columns are for Date (each day), Owner (the tech), Total Tickets (total for the day - all statuses) and Hours Worked (total for the day). If you *only* want to see closed tickets, you can play with this query (just change the S.NAME statement to whatever status you want):
select W.STOP as DATE, U.FULL_NAME as OWNER, COUNT(T.ID) as TOTAL_TICKETS,
sum(format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2)) as HOURS_WORKED
from HD_WORK W
join HD_TICKET T on (T.ID = W.HD_TICKET_ID)
join USER U on (U.ID = T.OWNER_ID)
join HD_STATUS S on (S.ID = T.HD_STATUS_ID)
where isnull(W.VOIDED_BY)
and W.STOP > DATE_SUB(NOW(), INTERVAL 31 DAY)
and S.NAME = 'closed'
group by OWNER, DATE
order by DATE, OWNER
John
You could also use/tweak this one, just add the HD_WORK columns to the inline views and reference in the main query if you want hours listed.
John
_________________________________
*Title*
Ticket Count by Owner (Current Year)
*Report Category*
Helpdesk (Custom)
*Description*
Lists ticket count by owner and status by month for current year.
*SQL Select Statement*
SELECT OPEN.OWNER, OPEN.MONTH, OPEN.YEAR, Coalesce(OPEN.OPEN, 0) AS OPENED, Coalesce(CLOSED.CLOSED, 0) AS CLOSED
FROM (SELECT Coalesce(U.USER_NAME, 'NO OWNER ASSIGNED') AS OWNER, date_format(T.CREATED, '%M') AS MONTH, YEAR (T.CREATED) AS YEAR, COUNT(*) AS OPEN
FROM HD_TICKET T
LEFT JOIN USER U ON T.OWNER_ID = U.ID
GROUP BY OWNER_ID, MONTH, YEAR
ORDER BY YEAR, MONTH) OPEN
LEFT JOIN (SELECT Coalesce(U.USER_NAME, 'NO OWNER ASSIGNED') AS OWNER, date_format(T.TIME_CLOSED, '%M') AS MONTH, YEAR (T.TIME_CLOSED) AS YEAR, COUNT(*) AS CLOSED
FROM HD_TICKET T
JOIN HD_STATUS S ON HD_STATUS_ID=S.ID and S.STATE ='Closed'
LEFT JOIN USER U ON T.OWNER_ID = U.ID
GROUP BY OWNER_ID, MONTH, YEAR
ORDER BY YEAR, MONTH) CLOSED
ON( OPEN.MONTH = CLOSED.MONTH AND OPEN.YEAR = CLOSED.YEAR AND OPEN.OWNER = CLOSED.OWNER )
ORDER BY YEAR desc, str_to_date(OPEN.MONTH,'%M') desc, OWNER
*Break on Columns*
MONTH
