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-->')),'')),
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 =
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 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.
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")