/build/static/layout/Breadcrumb_cap_w.png

Custom Reports in SQL

Hello,

My team and I are trying to assemble a custom report and having trouble with SQL as none of us are familiar. The report we're using now is working, but we need to be able to include total adjusted time of the each ticket as we only use adjusted time when recording. We have another report template that is successful in showing adjusted hours, but not most of the other details. Can the section of adjusted time be added and combined into the report we need? We've had no success ourselves. Thanks!


(Report we need)


SELECT HD_TICKET.ID, 

       HD_TICKET.CREATED, 

       HD_TICKET.TITLE, 

       HD_CATEGORY.NAME AS CATEGORY, 

       S.FULL_NAME AS SUBMITTER_NAME, 

       O.FULL_NAME AS OWNER_NAME, 

       HD_TICKET.DUE_DATE, 

       HD_TICKET.MODIFIED, 

       MACHINE.NAME AS SYSTEM_NAME, 

       HD_TICKET.ID as TOPIC_ID , 

CONCAT('TICK: ', HD_TICKET.ID) AS Number FROM HD_TICKET  

LEFT JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) 

LEFT JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) 

LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID 

LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) 

LEFT JOIN MACHINE ON (MACHINE.ID = HD_TICKET.MACHINE_ID) 

WHERE (HD_TICKET.HD_QUEUE_ID = '6') AND ((HD_TICKET.SUMMARY like '%EXAMPLE%') OR (HD_TICKET.CUSTOM_FIELD_VALUE0 like '%EXAMPLE%') AND (year(HD_TICKET.MODIFIED) = year(now())))  


(Report with Adjust Hours)


select U.FULL_NAME as 'Owner' , CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET, 

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(NOW(), INTERVAL 9 DAY)

order by U.FULL_NAME, W.STOP, T.CUSTOM_FIELD_VALUE2


2 Comments   [ + ] Show comments
  • It would help if you could define "adjusted time" for us, we might then be able to advise? - Hobbsy 2 years ago
  • Sure thing! In the ticket queue we use there's a tab to "Add Work" and in that is an entry box for Start Time, End Time, and Adjustment. This would be the offset of the total hours a ticket is worked. It appears this is a field that's available in the ticket queues by default and not custom. - Wede1993 2 years ago

Answers (0)

Be the first to answer this question

Don't be a Stranger!

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

Sign up! or login

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