/build/static/layout/Breadcrumb_cap_w.png

Work Report by Person

I am trying to alter a canned report from KACE called "Work Report Last 31 Days, By Person" to include the total hours worked for EACH technician. I have no SQL training and this is proving more difficult than expected. I may be doing this in a stupid way, but have no way of knowing the correct way to do this.

The canned report brings back all of the data I need, but I just need to total the time spent by EACH technician, my edited query seems to total everyone's time together. Any help is greatly appreciated.

The built-in KACE Query:

select W.STOP as DATE, CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET, U.FULL_NAME, substring(W.NOTE,1,1200) as NOTE,
format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2) as HOURS_WORKED
from (HD_WORK W, HD_TICKET T, USER U)
where W.HD_TICKET_ID = T.ID
  and isnull(W.VOIDED_BY)
  and W.USER_ID = U.ID
  and W.STOP > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by U.FULL_NAME, TICKET, W.STOP

My Edited Query:

select 
sq.DATE
, sq.TICKET
        , sq.FULL_NAME
, sq.NOTE
, sq.HOURS_WORKED
        , sq.TOTAL_HOURS_WORKED
    from
(
select
                W.STOP as DATE
                , CONCAT(CAST(T.ID AS CHAR) , " - ", T.TITLE) as TICKET
                , U.FULL_NAME
                , substring(W.NOTE,1,1200) as NOTE  
                , format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2) as HOURS_WORKED
                , (SUM(format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2))) as TOTAL_HOURS_WORKED
from (HD_WORK W, HD_TICKET T, USER U)
where W.HD_TICKET_ID = T.ID
                and isnull(W.VOIDED_BY)
                and W.USER_ID = U.ID
                and W.STOP > DATE_SUB(NOW(), INTERVAL 31 DAY)
) AS sq

group by sq.FULL_NAME, sq.TICKET

Again, any help is greatly appreciated.

 

2 Comments   [ + ] Show comments
  • The original report lists work on each ticket, but it sounds like you want to add a column for the total amount of work by technician. That would result in a report that shows a column that contains the same value in multiple rows, is that really what you want?

    If you want a report of total numbers of hours worked per technician, it would be better to have a separate report generate that data. Otherwise it might be confusing for the person reading the report. - chucksteel 6 years ago
    • Ideally I would like to add the total hours worked for each technician to the report break next to their user name.

      I am also not seeing where in the canned script it is indicating the report break. - Lazlo_Gonzo 6 years ago
      • Breaking on a column isn't part of the SQL statement, that is configured when you create the report on the appliance. - chucksteel 6 years ago
    • Hi Chuck,

      I have taken your advice and simplified my report looking for total work hours, and it works just fine.

      SELECT
      USER.USER_NAME
      , format(SUM(time_to_sec(timediff(W.stop, W.start)))/3600.0,2) as 'Hours Worked'
      FROM (HD_WORK W)
      JOIN USER on W.USER_ID = USER.ID
      WHERE W.STOP > DATE_SUB(NOW(), INTERVAL 31 DAY)
      GROUP BY W.USER_ID

      However, I would like to add a column that calculates the percent of tickets that have the column "CUSTOM_FIELD_VALUE1" from the table HD_TICKET set to "After Hours". I'm guessing something like this:

      SELECT
      USER.USER_NAME
      ,[PERCENTAGE FUNCTION?] T.CUSTOM_FIELD_VALUE1 as 'Work Type'
      , format(SUM(time_to_sec(timediff(W.stop, W.start)))/3600.0,2) as 'Hours Worked'
      FROM (HD_WORK W, HD_TICKET T)
      JOIN USER on W.USER_ID = USER.ID
      WHERE W.STOP > DATE_SUB(NOW(), INTERVAL 31 DAY)
      GROUP BY W.USER_ID

      But I don't really know how to get this calculation pulled off or how to fix my WHERE statement to specify the After Hours field.

      I'd really like to get all of this in 1 report as our previous ticket system was able to do it.

      Thanks! - Lazlo_Gonzo 6 years ago
      • Your previous ticket system might have been using a report designer that allowed for such things. The KACE database can be accessed with external tools, so you can setup other reporting tools, like CrystalReports, MS Access, etc. We have started to use PowerBI to create dashboards of our ticket and inventory data. I wrote a blog post with a simple example: https://chucksteel.blogspot.com/2017/09/linking-power-bi-to-quest-k1000-sma.htm - chucksteel 6 years ago
  • Ah, I see that now.

    KACE Pro Services had given me a quote to achieve what I had requested and I am trying to figure out how they would do it.

    Is this not actually possible? - Lazlo_Gonzo 6 years ago

Answers (0)

Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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