REPORT: After hours report
Hi Folks,
Looking to setup a simple report of afterhours tickets coming in. Can't seem to get the SQL to do what I want it to. Anyone have one like this already? Couldn't seem to find anything like it.
SELECT HD_TICKET.ID,S.FULL_NAME AS SUBMITTER_NAME,HD_TICKET.TITLE,O.FULL_NAME AS OWNER_NAME,HD_TICKET.CREATED FROM HD_TICKET LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ((TIMESTAMP(HD_TICKET.CREATED) <= NOW() AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 7 DAY)) AND ((TIMESTAMP(HD_TICKET.CREATED) >= DATE('22:00:00')) AND (TIMESTAMP(HD_TICKET.CREATED) <= DATE('07:30:00'))))
ORDER BY ID,TITLE,SUBMITTER_NAME
Cheers,
Tim
Answers (1)
Try this:
SELECT HD_TICKET.ID,S.FULL_NAME AS SUBMITTER_NAME
,HD_TICKET.TITLE
,O.FULL_NAME AS OWNER_NAME
,HD_TICKET.CREATED
FROM HD_TICKET
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID)
AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 7 DAY)
AND TIME(HD_TICKET.CREATED) > '22:00:00'
OR TIME(HD_TICKET.CREATED) < '07:30:00'
ORDER BY ID,TITLE,SUBMITTER_NAME
Comments:
-
Well, I removed the HD_Queue_ID since I only have one default queue. If it pulls the wrong information, fill that back in. - gcarpenter 12 years ago
