Summary Line for SQL Reports on the K1100
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
Answers (2)
Please log in to answer
Posted by:
chucksteel
9 years ago
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.
Posted by:
aragorn.2003
9 years ago
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)
Comments:
-
This is very close! Gives me a grand total, but not a sub total by tech. - tech@chch.org 9 years ago
