Excluding a particular ticket status in a specific date range in report
Hello Ninjas,
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.
TIA
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-->')),'')),
if(HD_TICKET_CHANGE.COMMENT='','',CONCAT(HD_TICKET_CHANGE.COMMENT,'\n'))
)
ORDER BY CAST( HD_TICKET_CHANGE.ID AS CHAR) DESC SEPARATOR '\n' ) AS
'Ticket Comments'
FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID =
HD_TICKET.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
WHERE HD_TICKET_CHANGE.OWNERS_ONLY=0
AND HD_TICKET.HD_QUEUE_ID=2
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
Answers (2)
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.
John
Comments:
-
Thanks John,
I get the same results with this line as I do with my other attempts. When this line is added to the Select I get only tickets with a Closed status. It doesn't return tickets with any other status. This result is what lead me to trying the EXCLUDE or the NOT IN. Either one doesn't exclude the appropriate tickets or excludes all status' except closed.
I think I need to find a DBA for this one ;)
Thanks again.
John - jmarotto 11 years ago -
No problem, I'll give it another shot when I have more time. If you get it before I do, please share.
John - jverbosk 11 years ago -
will do - jmarotto 11 years ago
-
I was able to resolve this by removing the 90 day time interval and asking for any status and then using NOT BETWEEN 'date and time' AND 'date and time' to exclude the tickets closed prior to the first of the month. It's not pretty but it runs in 0.171 sec and returns exactly what I was after.
Removed - AND HD_TICKET.CREATED > DATE_SUB(now(),INTERVAL 90 DAY)
Added. . .
AND ST.NAME != ''
AND HD_TICKET.TIME_CLOSED NOT BETWEEN '2012-01-01 00:00:00' AND '2012-07-01 00:00:00' - jmarotto 11 years ago
OK, think I may have gotten it this time, although I don't have any non-closed tickets older then 2 weeks to verify...
Try replacing this line:
AND HD_TICKET.CREATED > DATE_SUB(now(),INTERVAL 90 DAY)
With this:
AND IF (ST.NAME = 'closed', (DATEDIFF(NOW(),HD_TICKET.TIME_CLOSED) BETWEEN 31 AND 90), (HD_TICKET.CREATED > DATE_SUB(now(),INTERVAL 90 DAY)))
Results seem to match up with what you want (non-closed tickets for the past 90 days, closed tickets for past 31 - 90 days).
The only thing is sorting the results, but I'll see if this at least gets the data you want. If not, just let me know.
John
