Custom Report Help
I would like to create a helpdesk report that shows the amount work work logged on a ticket. I have created the following via the wizard, but it doesn't show me the work logged on the ticket only the notes.
SELECT HD_TICKET.ID, HD_TICKET.TITLE, O.FULL_NAME AS OWNER_NAME, HD_TICKET.CUSTOM_FIELD_VALUE0, HD_WORK.NOTE FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) LEFT JOIN HD_WORK ON (HD_WORK.HD_TICKET_ID = HD_TICKET.ID) WHERE (HD_TICKET.HD_QUEUE_ID = 2) AND (((TIMESTAMP(HD_TICKET.CREATED) <= NOW() AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 60 DAY))) AND (HD_WORK.NOTE is not null)) ORDER BY CUSTOM_FIELD_VALUE0, ID
We enter the amount of time we spend on a ticket, and are wanting to see what we are spending our time doing.
Answers (1)
Maybe this will help:
SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
O.FULL_NAME AS OWNER_NAME,
HD_TICKET.CUSTOM_FIELD_VALUE0,
TIMESTAMPDIFF(HOUR /*MINUTE*/ /*DAY*/, HD_WORK.START, HD_WORK.STOP) AS "HOURS TAKEN",
HD_WORK.ADJUSTMENT_HOURS,
HD_WORK.NOTE
FROM HD_TICKET
LEFT JOIN USER O
ON O.ID = HD_TICKET.OWNER_ID
LEFT JOIN HD_WORK
ON HD_WORK.HD_TICKET_ID = HD_TICKET.ID
WHERE HD_TICKET.HD_QUEUE_ID = 2
AND TIMESTAMP(HD_TICKET.CREATED) <= NOW()
AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(), INTERVAL 60 DAY)
AND HD_WORK.NOTE is not null
ORDER BY CUSTOM_FIELD_VALUE0, ID
Note on TIMESTAMPDIFF that you can change how the value is returned, right now it's returning hours, but can be minutes or days https://mariadb.com/kb/en/timestampdiff/
