Custom report for hours worked by adjustments
I need to create a report that presents the hours worked on each ticket, by adjustments as this is how we enter time. I need to display the ticket ID, the username, the category and subcategories, and a custom field that is a select box. Here is what I have been able to piece together so far.
SELECT USER.USER_NAME, W.HD_TICKET_ID, SUM(W.ADJUSTMENT_HOURS) as "Work Hours"
FROM ORG1.HD_WORK W
JOIN USER on W.USER_ID = USER.ID
WHERE W.STOP > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY W.HD_TICKET_ID
Any help would be appreciated.
Answers (1)
Top Answer
SELECT USER.USER_NAME, W.HD_TICKET_ID, SUM(W.ADJUSTMENT_HOURS) as "Work Hours",
HD_CATEGORY.NAME, HD_TICKET.CUSTOM_FIELD_VALUE0
FROM ORG1.HD_WORK W
JOIN HD_TICKET on HD_TICKET.ID = W.HD_TICKET_ID
JOIN HD_CATEGORY on HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
JOIN USER on W.USER_ID = USER.ID
WHERE W.STOP > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY W.HD_TICKET_ID
In the query above I am selecting the first custom field, which is stored in CUSTOM_FIELD_VALUE0 (the db columns are 0 based). You will need to adjust that to match your custom field.
Comments:
-
This is extremely helpful!
I ran this query and wasn't getting the custom field I was looking for and realized that it's the custom_2 under the ticket layout fields that I'm trying to grab. I tried to just change the value to 2 instead of 0 but that created an error. Also is there a way to grab the Title of the ticket as well? - HowDidYouKnow 4 years ago-
Custom field 2 will be CUSTOM_FIELD_VALUE1.
The title of the ticket is stored in the TITLE column, so you can add HD_TICKET.TITLE to the list of columns selected. - chucksteel 4 years ago-
That did the trick. I appreciate all you help, thank you! - HowDidYouKnow 4 years ago
