Currently working on a report that will display all New or Opened tickets that were Created more than 7 days ago and have certain Owners. It's working fine now and displays the "date created" but we would like a more usable column for something like "Days since Created". We'd like to replace TICKET.CREATED with a calculation that will display Days Open which will be the number of days since TICKET.CREATED.

This was created with the ticket wizard.

Any help is greatly appreciated.

SELECT HD_TICKET.CREATED AS HD_TICKET_CREATED,
O.FULL_NAME AS OWNER_NAME,
HD_STATUS.NAME AS STATUS,
S.FULL_NAME AS SUBMITTER_NAME,
HD_TICKET.ID AS HD_TICKET_ID,
HD_TICKET.TITLE AS HD_TICKET_TITLE,
HD_TICKET.CUSTOM_FIELD_VALUE6 AS HD_TICKET_CUSTOM_FIELD_VALUE6
FROM HD_TICKET
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 1)
AND ( (DATE(HD_TICKET.CREATED)<= DATE_SUB(NOW(), INTERVAL 7 day)
OR DATE(HD_TICKET.CREATED)> NOW())
AND (HD_STATUS.NAME LIKE '%new%'
OR HD_STATUS.NAME LIKE '%opened%')
AND HD_TICKET.CUSTOM_FIELD_VALUE1 NOT LIKE '%project%'
AND (O.FULL_NAME LIKE '%joyce%' OR O.FULL_NAME LIKE '%maurer%' or O.FULL_NAME LIKE '%herbert%' or O.FULL_NAME LIKE '%rasmussen%'))
ORDER BY O.FULL_NAME asc
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Try adding this to your select statement:
DATEDIFF(NOW(),HD_TICKET.CREATED) AS DAYS_SINCE_CREATED
Answered 12/01/2011 by: dchristian
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity