I want a report that will show me tickets that have been closed or opened on a specific day (to pull each day). The SQL I have will give me the tickets that were closed regardless of when they were opened (which is what I want) I just need it to also have the tickets opened today that were not closed. Any insight would be greatly appreciated.

select HD_TICKET.ID,
       HD_TICKET.TITLE ,
       HD_TICKET.DUE_DATE ,
       DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') as TIME_CLOSED,
       CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
              TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED),
              TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
              DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
              SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED)-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
              '%kh %im')) AS TIME_TO_CLOSE,
       HD_PRIORITY.NAME as PRIORITY,
       HD_CATEGORY.NAME as CATEGORY,
       HD_STATUS.NAME as STATUS,
       HD_IMPACT.NAME as IMPACT,
       MACHINE.NAME as MACHINE_NAME,
       ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
       (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 1 DAY)


order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL

Answer Summary:
Cancel
2 Comments   [ + ] Show Comments

Comments

  • Just to clarify you want a list of tickets that were opened today but not closed and a list of tickets there were closed today. Is that correct? And you want that in one report or are you asking for a separate report of tickets opened today and not closed?
  • you are correct, the report is to answer the question "what did you do today?" So I need to show every ticket that I worked on whether it got closed, opened, in progress etc. I would like this all in one report if possible
Please log in to comment

Answer Chosen by the Author

1

To answer "what did you do today?" you'll likely need more than just tickets opened and tickets closed.  Give this a try.  It pulls all ticket changes, including updates to open tickets that are not yet closed.

SELECT
    HD_TICKET.ID,
    HD_TICKET.TITLE,
    HD_TICKET.DUE_DATE,
    DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') AS TIME_CLOSED,
    CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
                TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED),
                TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1),
            'd ',
            DATE_FORMAT(ADDTIME('2000-01-01 00:00:00',
                            SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) - TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
                    '%kh %im')) AS TIME_TO_CLOSE,
    HD_PRIORITY.NAME AS PRIORITY,
    HD_CATEGORY.NAME AS CATEGORY,
    HD_STATUS.NAME AS STATUS,
    HD_IMPACT.NAME AS IMPACT,
    MACHINE.NAME AS MACHINE_NAME,
    IFNULL((SELECT
                    FULL_NAME
                FROM
                    USER
                WHERE
                    HD_TICKET.OWNER_ID = USER.ID),
            ' Unassigned') AS OWNER_NAME,
    (SELECT
            FULL_NAME
        FROM
            USER
        WHERE
            HD_TICKET.SUBMITTER_ID = USER.ID) AS SUBMITTER_NAME,
    HD_TICKET_CHANGE.TIMESTAMP AS Change_Timestamp,
    HD_TICKET_CHANGE.COMMENT AS Change_Comment,
    HD_TICKET_CHANGE.DESCRIPTION AS Change_Description,
    IFNULL((SELECT
                    FULL_NAME
                FROM
                    USER
                WHERE
                    HD_TICKET_CHANGE.USER_ID = USER.ID),
            ' Unassigned') AS OWNER_NAME
FROM
    HD_TICKET
        LEFT JOIN
    HD_CATEGORY ON HD_CATEGORY_ID = HD_CATEGORY.ID
        LEFT JOIN
    HD_STATUS ON HD_STATUS_ID = HD_STATUS.ID
        LEFT JOIN
    HD_PRIORITY ON HD_PRIORITY_ID = HD_PRIORITY.ID
        LEFT JOIN
    HD_IMPACT ON HD_IMPACT_ID = HD_IMPACT.ID
        LEFT JOIN
    MACHINE ON HD_TICKET.MACHINE_ID = MACHINE.ID
        LEFT JOIN
    HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
WHERE
    HD_TICKET_CHANGE.TIMESTAMP > DATE_SUB(NOW(), INTERVAL 1 DAY)

Answered 04/04/2016 by: grayematter
Fourth Degree Black Belt

  • Thank you, this is exactly what I needed!!!
Please log in to comment

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share