I have a report based on service desk that I need to run as if it was 1/2/2015. Is this even possible?
Answer Summary:
Cancel
4 Comments   [ + ] Show Comments

Comments

  • Can you provide more details on what the report is supposed to capture?
  • SELECT HD_TICKET.ID, HD_TICKET.TITLE AS Issue_Summary, HD_TICKET.CUSTOM_FIELD_VALUE4 AS Dept, HD_TICKET.CUSTOM_FIELD_VALUE14 AS Dept_Priority,HD_TICKET.CUSTOM_FIELD_VALUE13 AS Overall_Priority, S.FULL_NAME AS Customer, O.FULL_NAME AS Assigned_To, HD_TICKET.CREATED, HD_STATUS.NAME AS STATUS_NAME,HD_TICKET.CUSTOM_FIELD_VALUE11 AS Percent_Complete, HD_TICKET.CUSTOM_FIELD_VALUE12 AS Project_Progress, HD_TICKET.CUSTOM_FIELD_VALUE1 AS Work_Effort_Estimate, HD_TICKET.CUSTOM_FIELD_VALUE2 AS Severity, HD_TICKET.CUSTOM_FIELD_VALUE10 AS Ticket_Type,Q.NAME AS QUEUE_NAME FROM HD_TICKET LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ((HD_STATUS.NAME not like '%closed%'))
    UNION
    SELECT HD_TICKET.ID, HD_TICKET.TITLE AS Issue_Summary, HD_TICKET.CUSTOM_FIELD_VALUE2 AS Dept, HD_TICKET.CUSTOM_FIELD_VALUE1 AS Dept_Priority, HD_TICKET.CUSTOM_FIELD_VALUE3 AS Overall_Priority, S.FULL_NAME AS Customer, O.FULL_NAME AS Assigned_To, HD_TICKET.CREATED, HD_STATUS.NAME AS STATUS_NAME, HD_TICKET.CUSTOM_FIELD_VALUE4 AS Percent_Complete, HD_TICKET.CUSTOM_FIELD_VALUE5 AS Project_Progress, HD_TICKET.CUSTOM_FIELD_VALUE6 AS Work_Effort_Estimate, HD_TICKET.CUSTOM_FIELD_VALUE14 AS Blank5,HD_TICKET.IS_PARENT AS Ticket_Type, Q.NAME AS QUEUE_NAME FROM HD_TICKET LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID WHERE (HD_TICKET.HD_QUEUE_ID = 5) AND ((HD_STATUS.NAME not like '%closed%')) ORDER BY QUEUE_NAME, Assigned_To, ID
    • So are you looking to find tickets that were open on 1/2/2015?
  • Yes
  • Yes, I want ticket that were status = opened on that day. I don't necessarily want ticket that were created on that day.
Please log in to comment

Answer Chosen by the Author

0
To find tickets that were opened on a specific date you want to look for tickets that were created before or on that date, (DATE(CREATED) <= "2015-01-02") and closed after or on that date (DATE(TIME_CLOSED) >= "2015-01-02").

This basic query returns tickets that were open on 2015-01-02:
SELECT ID, TITLE, MODIFIED, CREATED, TIME_CLOSED FROM HD_TICKET
WHERE DATE(CREATED) <= "2015-01-02"
AND  DATE(TIME_CLOSED) >= "2015-01-02"

Answered 05/18/2015 by: chucksteel
Red Belt

Please log in to comment

Answers

0
If you use the provided SQL statement you can add a where condition based on the HD_TICKER.CREATED date.

WHERE HD_TICKET.CREATED <= '2015-02-01 00:00:00'
Answered 05/18/2015 by: aragorn.2003
Red Belt

  • or WHERE HD_TICKET.CREATED = '2015-02-01 00:00:00' if you want tickets that are opened on this day.
Please log in to comment
Answer this question or Comment on this question for clarity