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
Answers (0)
Please log in to answer
Be the first to answer this question
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
I am also not seeing where in the canned script it is indicating the report break. - Lazlo_Gonzo 6 years ago
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
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