Excluding a particular ticket status in a specific date range in report
I had struggled with this and posted a question some time back. I settled on a resolution that worked but was not perfect. The ticket report provides ID, created and closed timestamps, status, title, comments that are not flagged as owner, submitter and owner full name and, captures department name and department number. The requirement is for all of this infomation but only show tickets closed in the last 7 days.
I was able to meet this requirement by using "AND HD_TICKET.TIME_CLOSED NOT BETWEEN '2012-01-01 00:00:00' AND '2012-09-21 00:00:00'". When the report was run weekly this solution worked well. The requestor now wants it daily and I cannot manually make the date change every day, no time for it.
I have gotten to here. . .
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 ST.NAME != ''
AND HD_TICKET.TIME_CLOSED < (CURDATE() -INTERVAL 7 DAY)
AND HD_TICKET.CUSTOM_FIELD_VALUE1 IN (long list of department numbers)
GROUP BY HD_TICKET_ID
ORDER BY Status DESC
This returns all tickets except those with a time_closed value in the last 7 days. I need the opposite result - all tickets and just those closed in the last 7 days. Changing "AND HD_TICKET.TIME_CLOSED < (CURDATE() -INTERVAL 7 DAY)" using any operator other than "<" returns either just closed tickets or nothing. I've got too many hours on this already and would appreciate any assistance.
Thanks
Answers (1)
Right, you're going to need to have the two parts to the statement that are in parenthesis. Try this: and (HD_TICKET.TIME_CLOSED BETWEEN (CURDATE() -INTERVAL 7 DAY) and NOW() or HD_TICKET.TIME_CLOSED = "0000-00-00 00:00:00") The 0000-00-00 00:00:00 is what is in the TIME_CLOSED column for tickets that are still open. This should match tickets that were closed in the past seven days or tickets that are still open. You could also use HD_STATUS.NAME = "Open": and (HD_TICKET.TIME_CLOSED BETWEEN (CURDATE() -INTERVAL 7 DAY) and NOW() or HD_STATUS.NAME = "Open")
Try this:
HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
Comments:
-
Thanks Chuck but I get the same output - only tickets with a status of Closed and a Closed time=less than 7 days back.
These are the tickets I'm trying to INCLUDE, along with all active tickets. I've tried multiple variations of that line without success, in fact, if you reverse the operator (from > to <) the return is all active tickets and tickets that were closed more than 7 days ago.
It's just odd. The only thing that has ever worked is the "HD_TICKET.TIME_CLOSED NOT BETWEEN '2012-01-01 00:00:00' AND '2012-09-30 00:00:00'" Using that returns all active tickets regardless of status/state and only the Closed tickets from the last 7 days. - jmarotto 12 years ago -
Can you use HD_TICKET.TIME_CLOSED BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) and NOW()?
I think I might not understand what you're looking for. Do you want all active tickets and those that were closed in the past seven days? - chucksteel 12 years ago -
Yes. The request from the department director is - I need to see all active tickets submitted by people in my departments AND which of those tickets were closed in the last week. I've had to learn not to ask why but simply provide them with the data ;)
I assume he is using the closed ticket data to track how long it takes, for issues reported by his teams, to be resolved. Getting this on a daily basis he would be able to create trending data. Who knows. - jmarotto 12 years ago -
Just ran your edit with the same results. Return is - only tickets with status of Closed with a Closed timestamp within the last seven days. - jmarotto 12 years ago
-
Right, you're going to need to have the two parts to the statement that are in parenthesis. Try this:
and (HD_TICKET.TIME_CLOSED BETWEEN (CURDATE() -INTERVAL 7 DAY) and NOW() or HD_TICKET.TIME_CLOSED = "0000-00-00 00:00:00")
The 0000-00-00 00:00:00 is what is in the TIME_CLOSED column for tickets that are still open. This should match tickets that were closed in the past seven days or tickets that are still open. You could also use HD_STATUS.NAME = "Open":
and (HD_TICKET.TIME_CLOSED BETWEEN (CURDATE() -INTERVAL 7 DAY) and NOW() or HD_STATUS.NAME = "Open") - chucksteel 12 years ago -
Nailed it! Thanks so much Chuck I really appreciate. Another SQL item off my plate.
FYI. . .I had to use the TIME_CLOSED = "000-00-00 00:00:00". The HD_STATUS.Name="Open" returned the opposite result.
Thanks again. - jmarotto 12 years ago
