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

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

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.

Answered 10/22/2012 by: chucksteel
Red Belt

Please log in to comment
1

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

Answered 11/11/2012 by: KOLDY
Green Belt

Please log in to comment
Answer this question or Comment on this question for clarity