/build/static/layout/Breadcrumb_cap_w.png

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

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
Posted by: dchristian 10 years ago
Red Belt
0
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 10 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 10 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ