Excluding a particular ticket status in a specific date range in report
I'm using the following query to get a report for a department director. He's been most satisfied with the output so far, until today. He asked if I could -exclude tickets in a closed status that were closed greater then 30 days ago.
The quiry grabs all tickets, regardless of status, submitted by users in the departments he manages over a 90 day period.
I have tried to EXCLUDE and/or NOT IN (SELECT . . .) without success.
Any suggestions would be most welcomed.
SELECT HD_TICKET.ID as Number, HD_TICKET.CREATED as Opened, ST.NAME as Status, HD_TICKET.TIME_CLOSED as Closed, HD_TICKET.TITLE, S.FULL_NAME AS Submitter, O.FULL_NAME AS 'Ticket Owner', HD_TICKET.CUSTOM_FIELD_VALUE0 as Department, HD_TICKET.CUSTOM_FIELD_VALUE1 as 'Dept#',
GROUP_CONCAT( CONCAT('\n-- Change by ',U.USER_NAME,' on ',
CAST(DATE(HD_TICKET_CHANGE.TIMESTAMP) AS CHAR),' ---\n',
IF(DESCRIPTION='' OR ISNULL(DESCRIPTION),'',
CONCAT('-->',TRIM(TRAILING '-->' FROM REPLACE(DESCRIPTION,'\n','\n-->')),'')),
ORDER BY CAST( HD_TICKET_CHANGE.ID AS CHAR) DESC SEPARATOR '\n' ) AS
FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID =
JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
LEFT join HD_STATUS ST on ST.ID=HD_TICKET.HD_STATUS_ID
LEFT JOIN USER U ON USER_ID=U.ID
LEFT JOIN USER S ON S.ID=HD_TICKET.SUBMITTER_ID
AND HD_TICKET.CREATED > DATE_SUB(now(),INTERVAL 90 DAY)
AND HD_TICKET.CUSTOM_FIELD_VALUE1 IN (18047,52010,52011,52012,9208)
GROUP BY HD_TICKET_ID
ORDER BY Status
Community Chosen Answer
Try adding this to your WHERE statement (in between any of the AND statements would work - but aesthetically, I'd put it after the INTERVAL 90 DAY one):
AND (ST.NAME = 'closed' AND DATEDIFF(NOW(),HD_TICKET.TIME_CLOSED) < 30)
It seemed to work when I ran it (after stripping away the stuff specific to your queue), but if it doesn't do what you want just let me know and I'll take another crack at it.