How can I add a Work Total column to the Work Report?
Below is the code I would like to use. I like the format just fine, but I would like to add a column that totals the hours for the week per user.
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 7 DAY)
order by U.FULL_NAME, TICKET, W.STOP
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 7 DAY)
order by U.FULL_NAME, TICKET, W.STOP
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
SELECT CONCAT('Hours worked since ',DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) as SUMMARY, 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(CURRENT_DATE(), INTERVAL 7 DAY) GROUP BY U.FULL_NAME
SELECT CONCAT('Hours worked since ',DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) as SUMMARY, 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(CURRENT_DATE(), INTERVAL 7 DAY) GROUP BY U.FULL_NAME
Please log in to answer
Posted by:
dchristian
12 years ago
Hmmm.... I think that'll look a lil weird. Would about more of a summary report, with just the user and the week?
Comments:
-
That would work to. I could always run two reports for that. This is something that would be passed on to upper management so I would like it to be as complete as possible. Perhaps just totaling the whole column for all Ticket Owners. Is there a way to total/SUM up the Hours_Worked Column and set the output in the report? - Hammer1790 12 years ago
-
Hey Hammer,
Try this out:
SELECT CONCAT('Hours worked since ',DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) as SUMMARY,
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(CURRENT_DATE(), INTERVAL 7 DAY)
GROUP BY U.FULL_NAME - dchristian 12 years ago
