/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

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