Hi,

I was wondering if it's possible to show the working hours per week of a specific user on the K1000.

We are using the Service Desk system and I would like that every technicians gets their total hours per week from every tickets of every week.

Is it possible to do that? By looking at the forum, it seems that it's possible with a script on the K1000. I'm a SQL expert but I understand the basics.

I've found this thread but I didn't succeed to apply it : http://www.itninja.com/blog/view/k1000-report-working-hours-per-week-per-person

If anyone can help it would be really appreciated.

Thank you.
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1
Here is a basic query that will show work on tickets in the past seven days for a given user name:
SELECT USER.USER_NAME, W.HD_TICKET_ID, SUM(DATEDIFF(W.STOP, W.START))
FROM ORG1.HD_WORK W
JOIN USER on W.USER_ID = USER.ID
WHERE W.STOP > DATE_SUB(NOW(), INTERVAL 7 DAY)
and USER.USER_NAME = "username"
GROUP BY W.HD_TICKET_ID
Substitute the user's name to get the report for a specific user. If you want all users then you can remove that line. If you don't want to view the data per ticket you can change the GROUP BY statement to USER.ID and get a total per user. 

This query assumes that your technicians are entering start and stop hours for their work. If you are instead using adjustment hours then you need to sum those using SUM(W.ADJUSTMENT_HOURS).

Answered 01/07/2016 by: chucksteel
Red Belt

  • Hi,

    Thanks for the reply. When I create the report with what you wrote, it shows me that: http://s14.postimg.org/x1uggz3b3/SSKace.png

    It shows 0 hours and the sections are named "Hd Ticket Id" and "Sum(Datediff(W.stop, W.start))"
    Sorry for my lack of knowledge, maybe I'm doing something wrong or I didn't understand something.

    For now, the user "Kenny Ngo' just has 1 ticket with 2 Work hours lines:
    http://s1.postimg.org/x6kqctxpb/SSKace2.png

    Thanks again.
Please log in to comment

Answers

0
Sorry, I was using datediff when I should have used timestampdiff:
SELECT USER.USER_NAME, W.HD_TICKET_ID, SUM(TIMESTAMPDIFF(HOUR, W.START, W.STOP)) as "Work Hours"
FROM ORG1.HD_WORK W
JOIN USER on W.USER_ID = USER.ID
WHERE W.STOP > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY W.HD_TICKET_ID
Also, if you haven't already setup a tool like MySQL Workbench to look at the SQL tables I highly recommend it. It makes building queries like this and debugging them much easier.

Answered 01/08/2016 by: chucksteel
Red Belt

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