Work Report last 31 days by person - Sum Hours
Hello!
We have recently started using the kbox for ticketing. My bosses really like this report, Work Report last 31 days by person, but they want it to sum the hours for each user.
I have created a different report, but they would like it all in one.
Advice?
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
Answers (2)
Try this and see if it works
SELECT
U.ID AS USER_ID,
U.FULL_NAME,
COUNT(*) AS TOTAL_TICKETS,
SUM(FORMAT((TIME_TO_SEC(TIMEDIFF(W.STOP, W.START)))/3600.0 + W.ADJUSTMENT_HOURS, 2)) AS TOTAL_HOURS_WORKED
FROM
HD_WORK W
INNER JOIN
HD_TICKET T ON W.HD_TICKET_ID = T.ID
INNER JOIN
USER U ON W.USER_ID = U.ID
WHERE
ISNULL(W.VOIDED_BY)
AND W.STOP > DATE_SUB(NOW(), INTERVAL 31 DAY)
GROUP BY
U.ID, U.FULL_NAME
ORDER BY
U.ID
Thank you this is very nice! Is there a way to add in the the ticket break down as well by hour?
This is outside my skill level, and I really appreciate the help!