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
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
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
Hmmm.... I think that'll look a lil weird. Would about more of a summary report, with just the user and the week?
Answered 03/20/2012 by: dchristian
Red Belt

  • 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?
  • 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
Please log in to comment
Answer this question or Comment on this question for clarity