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

Comments

  • It's not a complete answer, that would have to be a custom report built from scratch but....

    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
    • That's almost what I need. Thank you. But I still would like a proper report.
  • Anyone any clue how to do the final part of this question?
Please log in to comment

Answers

0
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)

Answered 06/26/2015 by: chucksteel
Red Belt

  • That's fantastic.
    How do I get it to only show today instead of all days?

    Where Date = (Today)

    Thanks,
    • 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)
      • Again, fantastic. Now how to we get the answer in minutes to 0 decimal places?
      • Since we don't use that field I'm not sure what the data normally looks like. How is it shown in the report?
      • 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.
      • This content is currently hidden from public view.
        Reason: Removed by member request
        For more information, visit our FAQ's.
      • 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"
      • 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,
      • 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
      • I have no idea on how to do this. Is it possible?
Please log in to comment
Answer this question or Comment on this question for clarity