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.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1

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

Answered 09/11/2012 by: jverbosk
Red Belt

Please log in to comment

Answers

0

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

Answered 09/11/2012 by: jverbosk
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity