/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

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