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
Fifth 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