/build/static/layout/Breadcrumb_cap_w.png

Help displaying # of days a ticket has been open

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

Answers (1)

Posted by: dchristian 13 years ago
Red Belt
0
Try adding this to your select statement:
DATEDIFF(NOW(),HD_TICKET.CREATED) AS DAYS_SINCE_CREATED
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ