How can you sum the total work hours per owner per day? I used the "Work Report lat 31 days by person" as my base and tweaked it a bit but can't seem to figure out how to sum the work hours per day per owner.

Here is the code:

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 1 DAY)
order by U.FULL_NAME, DATE, TICKET, W.STOP
Break on Columns: FULL_NAME
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Hey jkeister,

Let me know if this works out for you.

select DATE_FORMAT(W.STOP,'%Y-%m-%d') as DATE,
U.FULL_NAME,
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 31 DAY)
GROUP BY DATE,
FULL_NAME
order by U.FULL_NAME, DATE, W.STOP
Answered 01/20/2012 by: dchristian
Red Belt

Please log in to comment
0
David,

Thank you sooooo much this is very helpful.
Just would like to show each ticket with time per ticket with a total for the end of the day.
Like this:
# Date Ticket Hours Worked
Full Name: John Smith (Owner)
1 1/19/2012 9:30 122 - iPad Request 1
2 1/19/2012 9:30 122 - iPad Request 0.5
3 1/19/2012 9:35 110 - *Misc Tasks (Non Help Desk) 1.5
Total Hours for the day 3
Answered 01/20/2012 by: jkeister
Senior Yellow Belt

Please log in to comment
Answer this question or Comment on this question for clarity