/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (1)

Posted by: Michelle.ashby 3 years ago
Purple Belt
0

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)

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ