Need help writing a custom report
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
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
5 years ago
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.
Comments:
-
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. - tbingeman 5 years ago
-
Thank you for your quick response. This is exactly what I am looking for. - tbingeman 5 years ago
Posted by:
chucksteel
5 years ago
Top Answer
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.
