Hours Worked Report (including users with 0 hours)
I'm attempting to build a report with a 7 day interval that only includes closed tickets. It does, however, need to include users that are not logging work using the add work option. While this code is for a summary report, I would like to also have a detail report with the hours worked information. Once again, it seems that every report I run does not include any null values in the hours worked field. I've tested this and have determined the results do not include tickets that did not have the add work option updated.
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
Answers (2)
Instead of limiting the tickets where W.STOP is within the past seven days look for tickets that were closed within the past seven days. That should then include all tickets that were closed in the past seven days regardless of whether they have informtion in the HD_WORK table.
We are using this report:
Title: Closed Ticket Resolutions last 7 days by Owner
Category: HelpDesk
Description: Weekly Tracker for Help Desk Techs tickets and work
output html,csv,text
SQL:
select
HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.RESOLUTION ,
SUM(TIMESTAMPDIFF(MINUTE,HD_WORK.START,HD_WORK.STOP)) + ROUND(SUM(HD_WORK.ADJUSTMENT_HOURS)*60) as TOTAL_TIME_WORKED_in_min,
DATE(HD_TICKET.TIME_CLOSED) as DATE_CLOSED,
HD_CATEGORY.NAME as CATEGORY,
TECH.FULL_NAME as TECH_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from
HD_WORK left join HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
on HD_WORK.HD_TICKET_ID = HD_TICKET.ID
left join USER as TECH
on HD_WORK.USER_ID = TECH.ID
where
HD_WORK.STOP > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY
HD_WORK.USER_ID,
HD_WORK.HD_TICKET_ID
order by
TECH_NAME,
HD_PRIORITY.ORDINAL,
HD_CATEGORY.ORDINAL,
HD_STATUS.ORDINAL,
HD_IMPACT.ORDINAL
Break on Columns: TECH_NAME
Show Line Number Column: x
