K1000 work report by Submitter (service desk)
Hi,
Has anyone got a report built to show the time spent for each submitter?
Even better, it would be great to pull the submitter's Department through and calculate the total Work against the Department.
I can't get Work to show in the Wizard report builder and SQL report writing is not my strength.
Thanks
Damien
Answers (1)
OK, I have something that may be a good start...
I have a "Closed IT Tickets for previous week with Time Worked" report. I export the results and using Excel can add headers and sort and tally thing pretty easy. Not exactly what you are looking for, but like I said a start.
SELECT
HD_TICKET.ID,
HD_TICKET.CREATED,
HD_TICKET.TIME_CLOSED,
HD_CATEGORY.NAME AS CATEGORY,
HD_TICKET.TITLE,
S.FULL_NAME AS SUBMITTER_NAME,
HD_TICKET.CUSTOM_FIELD_VALUE0 AS Division,
O.FULL_NAME AS OWNER_NAME,
format(
(time_to_sec(timediff(stop, start))) / 3600.0 + ADJUSTMENT_HOURS,
2
) as HOURS_WORKED,
format(
(time_to_sec(timediff(stop, start))) / 60 + (ADJUSTMENT_HOURS * 60),
0
) as MINUTES_WORKED
FROM
HD_TICKET
LEFT JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
LEFT JOIN HD_WORK ON (HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
WHERE
(HD_TICKET.HD_QUEUE_ID = 5)
AND (HD_TICKET.TIME_CLOSED IS NOT NULL)
AND (
(
(
HD_TICKET.TIME_CLOSED >= curdate() - INTERVAL DAYOFWEEK(curdate()) + 6 DAY
AND HD_TICKET.TIME_CLOSED < curdate() - INTERVAL DAYOFWEEK(curdate()) -1 DAY
)
)
)
ORDER BY
CATEGORY,
OWNER_NAME,
CREATED
** Change ORDER BY to be S.FULL_NAME to sort by submitter, but like I said, after data is in Excel you can manipulate all this easily enough**** you should change the queue ID under the WHERE clause or remove it if you want all queues & the timestamp can be changed to be for a different period if wanted**
Also, for reference, it is kind of old, but when I work within SQL I reference this a lot to get the stuff I need: schema_bootkamp.jpg (2690×1867) (foreignkid.net)
