/build/static/layout/Breadcrumb_cap_w.png
04/27/2018 523 views
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

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.
    • 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.
      • Breaking on a column isn't part of the SQL statement, that is configured when you create the report on the appliance.
    • 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!
      • 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
  • 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?

There are no answers at this time