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

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

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?
Answered 11/30/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
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
Answered 11/30/2010 by: brondum9
Senior Yellow Belt

Please log in to comment
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?
Answered 11/30/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
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
Answered 11/30/2010 by: brondum9
Senior Yellow Belt

Please log in to comment
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
Answered 12/01/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Under owner name I only recieve a number one and not the owners'/pctechs' name.
Answered 12/01/2010 by: brondum9
Senior Yellow Belt

Please log in to comment
0
you could try full name if you prefer. O.FULL_NAME
Answered 12/01/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
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
Answered 12/01/2010 by: brondum9
Senior Yellow Belt

Please log in to comment
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.
Answered 12/01/2010 by: GillySpy
Seventh Degree Black Belt

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