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

Please log in to comment

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

Please log in to comment
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.
Please log in to comment
Answer this question or Comment on this question for clarity

Share