Service Desk report, per Engineer, per day, 360 minutes time worked
Hi,
We have 5 Engineers and we want to ensure they are logging about 360 minutes per day. They can add time albeit not in a way I'd like on the ticketing system but I'm wondering how we can report on this so they can run a report daily that shows the amount of time each engineer has spent per day.
The scenario is as such:
Engineer logs a ticket (or it's gets emailed in).
They assign themselves as an owner.
The add some work to the ticket, say 10 minutes. (but click the Add work and clicking 00:10 on the start time box (I know, not ideal)
Then they save it.
They look through other tickets they have and do work on them and each time add a duration to the ticket.
I need a count that counts up all those time additions per day and reports it for each engineer.
Make sense?
Thanks,
Jonny.
2 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
8 years ago
If you have the techs enter their hours under Adjustment Hours then you can use a report like this:
SELECT USER_ID, USER.FULL_NAME, DATE(HD_WORK.MODIFIED), SUM(ADJUSTMENT_HOURS) FROM ORG1.HD_WORK
JOIN USER on USER.ID = HD_WORK.USER_ID
GROUP BY USER_ID, DATE(HD_WORK.MODIFIED)
Comments:
-
That's fantastic.
How do I get it to only show today instead of all days?
Where Date = (Today)
Thanks, - JonnyBarr 8 years ago-
SELECT USER_ID, USER.FULL_NAME, DATE(HD_WORK.MODIFIED), SUM(ADJUSTMENT_HOURS) FROM ORG1.HD_WORK
JOIN USER on USER.ID = HD_WORK.USER_ID
WHERE DATE(HD_WORK.MODIFIED) = DATE(NOW())
GROUP BY USER_ID, DATE(HD_WORK.MODIFIED) - chucksteel 8 years ago-
Again, fantastic. Now how to we get the answer in minutes to 0 decimal places? - JonnyBarr 8 years ago
-
Since we don't use that field I'm not sure what the data normally looks like. How is it shown in the report? - chucksteel 8 years ago
-
It looks like this:
Sum(Adjustment Hours)
0.8000000268220901
So each 0.1 = 6 minutes.
I will be instruction the engineers t update their time in 6 minute increments.
So 0.2 =12 mins, 0.3 = 18 mins and so on.
happy if it round up to 0 decimal places. - JonnyBarr 8 years ago -
You should be able to convert the time to seconds and then use sec_to_time to display as hours:minute:seconds. So instead of SUM(ADJUSTMENT_HOURS) you would have:
sec_to_time(SUM(ADJUSTMENT_HOURS)*3600) AS "Work Time" - chucksteel 8 years ago -
We are nearly there. Great stuff.
Now shows as:
Work Time
00:48:00.000096
Can we truncate and remove the seconds and just have it as a total count in minutes.
I've added 18 minutes to the total and now it shows as
Work Time
01:06:00.000112
Can this show as:
Work Time
66 minutes
Thanks, - JonnyBarr 8 years ago -
Check the syntax for the time_format function:
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_time-format - chucksteel 8 years ago -
I have no idea on how to do this. Is it possible? - JonnyBarr 8 years ago
If you take the standard report "Work Report last 31 days by person" and duplicate it.
In the duplicate, add to the "BREAK ON COLUMNS" box ",DATE" it will show you time worked by date and Technician, which could at least as an interim be dumped into Excel as a CSV and totalled - Hobbsy 8 years ago