Report - Total Work Hours per day
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
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
Answers (2)
Please log in to answer
Posted by:
dchristian
12 years ago
Hey jkeister,
Let me know if this works out for you.
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
Posted by:
jkeister
12 years ago
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
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
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.