/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Summary Line for SQL Reports on the K1100

03/26/2015 1218 views
Is there a way I can add in a summary line to total the number of hours for each tech on this report?  I know nothing about SQL.  

select W.STOP as DATE, 
format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2) as HOURS_WORKED,
CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET,
 U.FULL_NAME, substring(W.NOTE,1,1200) as NOTE
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 7 DAY)
order by U.FULL_NAME, W.STOP
0 Comments   [ + ] Show comments

Comments


All Answers

0
You can't really do this with just a SQL statement, unfortunately. If you used a full blown reporting tool like Crystal Reports that would be possible but I don't believe KACE includes this functionality natively.

Answered 03/27/2015 by: chucksteel
Red Belt

0
It´s not a good way but it is possible. You can do this with a union statement.

select W.STOP as DATE, format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2) as HOURS_WORKED, CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET,  U.FULL_NAME, substring(W.NOTE,1,1200) as NOTE
  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 7 DAY)
union
select 'total', sum(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 7 DAY)
Answered 03/27/2015 by: aragorn.2003
Red Belt

  • This is very close! Gives me a grand total, but not a sub total by tech.

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