/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Need help writing a custom report

01/24/2019 349 views

I am looking to try and combine the following 2 reports into one report that shows all the tickets that have been closed within a given time period and showing how much work each technician has worked on each ticket.


select HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.TIME_CLOSED as TIME_CLOSED,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
MACHINE.NAME as MACHINE_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_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 MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
order by HD_TICKET.ID, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL



SELECT
USER.USER_NAME,
format(SUM(time_to_sec(timediff(W.stop, W.start)))/3600.0,2) as 'Hours Worked'
FROM (HD_WORK W)
JOIN USER on W.USER_ID = USER.ID
WHERE W.STOP > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY W.USER_ID
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0

This query will return multiple rows per ticket when there are multiple workers on the ticket. Note that we don't track hours worked so I have very limited data in my appliance to sanity check this.

select HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.TIME_CLOSED as TIME_CLOSED,
HD_PRIORITY.NAME as PRIORITY, 
HD_CATEGORY.NAME as CATEGORY, 
HD_STATUS.NAME as STATUS,
MACHINE.NAME as MACHINE_NAME,
WORKER.FULL_NAME as 'WORKER',
SUBMITTER.FULL_NAME as SUBMITTER_NAME
,format(SUM(time_to_sec(timediff(W.stop, W.start)))/3600.0,2) as 'Hours Worked'
from 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 MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
left join HD_WORK W on W.HD_TICKET_ID = HD_TICKET.ID
left join USER WORKER on WORKER.ID = W.USER_ID
LEFT JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY HD_TICKET.ID, W.USER_ID
order by HD_TICKET.ID, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL

I also changed your sub select statements and used joins instead.

Answered 01/25/2019 by: chucksteel
Red Belt

All Answers

0

Here you go:

select HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.TIME_CLOSED as TIME_CLOSED,
HD_PRIORITY.NAME as PRIORITY, 
HD_CATEGORY.NAME as CATEGORY, 
HD_STATUS.NAME as STATUS,
MACHINE.NAME as MACHINE_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
,format(SUM(time_to_sec(timediff(W.stop, W.start)))/3600.0,2) as 'Hours Worked'
from 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 MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
left join HD_WORK W on W.HD_TICKET_ID = HD_TICKET.ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY HD_TICKET.ID
order by HD_TICKET.ID, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL


To accomplish this I added a join statement to the HD_WORK table. The only other trick is to group by ticket ID, otherwise the query will sum all of the data from HD_WORK into one row.

Answered 01/25/2019 by: chucksteel
Red Belt

  • This content is currently hidden from public view.
    Reason: Removed by member request For more information, visit our FAQ's.
  • Your solution worked to a point. While it did show the total amount of time worked on the ticket, it did not show each technician that worked on the ticket and their total time on the ticket. We really need to find out how much time any technician worked on the ticket.
  • Thank you for your quick response. This is exactly what I am looking for.

Don't be a Stranger!

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

Sign up! or login

View more:

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