/build/static/layout/Breadcrumb_cap_w.png

HELP WITH HELPDESK REPORT

I need to be able to generate reports for one day (run at 11:55pm everynight) that shows the owner that closed it and how much time that owener input into add work. How much time did it take the owner to complete not to close.

0 Comments   [ + ] Show comments

Answers (9)

Posted by: GillySpy 14 years ago
7th Degree Black Belt
2
Something like this:
select O.USER_NAME "Owner Name", COUNT(T.ID) "Tickets Closed",
cast(SUM((UNIX_TIMESTAMP(STOP)-UNIX_TIMESTAMP(START)+(ADJUSTMENT_HOURS*60*60)) / 60/69) as DECIMAL(4,2)) "Total Time"
from HD_TICKET T
JOIN HD_STATUS S ON S.ID=T.HD_STATUS_ID
JOIN HD_WORK W ON W.HD_TICKET_ID=T.ID
LEFT JOIN USER O ON OWNER_ID=O.ID
/*
WHERE
S.STATE='Closed' and
DATE(TIME_CLOSED) = CURDATE() /* since your running the report before midnight this works */ and
DATE(W.START) =CURDATE()
GROUP BY O.ID
ORDER BY 1
Posted by: GillySpy 14 years ago
7th Degree Black Belt
0
Clarifications on your question:
  • Can the owner that closed it be different then the owner on the ticket? Is that important here?
  • are you wanting a total of all work entries? or specific ones related to closing the ticket? if relevant, how do you mark the difference?
  • when does the "clock" start ticking? or will your work entries contain all the appropriate start and stop information for the calculation?
Posted by: brondum9 14 years ago
Senior Yellow Belt
0
yes it can be a different owner that assigns time
closed tickets for one day. No all closed tickets
6am till 6pm are the usual hours
Posted by: GillySpy 14 years ago
7th Degree Black Belt
0
I'll try to rephrase as I'm not certain I understand yet.
  • The owner of a closed ticket when the report is run can be different then the owner who close the ticket?
  • Can the lifespan of a ticket go beyond 1 day? If so, are you interested in tickets that were closed in the last 24 hours or opened and closed in the last 24 hours?
  • Are you wanting a total of all work entries over the last 24 hours or the ticket's life span?
  • Are you wanting to calculate time spent on all works entries? Or only interested in time spent on specific work entries related to closing the ticket? if the latter, how do you mark the difference?
  • What data points do you want to be involved in the calculation? will your work entries contain all the appropriate start and stop information for the calculation and simply need to total the differences (sum of each work entry total) or do you want to include the tiem the ticket was opened? if so how?
Posted by: brondum9 14 years ago
Senior Yellow Belt
0
So if the tech/owner of the ticket puts his/her time, it doesnt matter to me whom closed it.
ONe day only
total closed tickets for 24hours
time to complete a ticket (.25 hours) and total hours worked per owner for that day.

Owner NAME TIckets Closed Total time
PCTECH 5 7.75 hours
Posted by: brondum9 14 years ago
Senior Yellow Belt
0
Under owner name I only recieve a number one and not the owners'/pctechs' name.
Posted by: GillySpy 14 years ago
7th Degree Black Belt
0
you could try full name if you prefer. O.FULL_NAME
Posted by: brondum9 14 years ago
Senior Yellow Belt
0
The below is a the report Cloase tickets last 7 days by owner. Its only missing 2 things that I need and that is the Total time tally per owner and the one day report instead of the seven.
Is their a way to modify this to make it a one day report and tally the time per owner? Our Pc Tech Manager needs to have a report each day showing everything below with total time worked for each Tech/owner based on the tickets he/she completes each day. They should have completed 8 hours of workorders each day.



select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE ,
DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') as TIME_CLOSED,
CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED)-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
'%kh %im')) AS TIME_TO_CLOSE,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
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 HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.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 1 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
Posted by: GillySpy 14 years ago
7th Degree Black Belt
0
The challenge is that the reports don't display the same data. So you could do some custom jasper layout to summarize the data or you could run the two reports and then you'd have the data you need.
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

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