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

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

2

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

Answered 11/14/2012 by: gcarpenter
Green Belt

  • Well, I removed the HD_Queue_ID since I only have one default queue. If it pulls the wrong information, fill that back in.
Please log in to comment
Answer this question or Comment on this question for clarity