I have a custom report that I run against a specific set of users in our helpdesk team. I've tried to get the report to run like the canned report that kicks out open tickets by owner for the last 7 days, but the code I'm adding keeps giving an error. Can someone please look at my code here and propse a solution that would have it run for a 7 day cycle only as opposed to all open tickets?

 

SELECT HD_TICKET.ID,
      HD_QUEUE.NAME,
      HD_TICKET.TITLE,
      HD_TICKET.DUE_DATE,
      Concat(IF(Time_to_sec(NOW()) >= Time_to_sec(HD_TICKET.TIME_OPENED), To_days(NOW()) - To_days(HD_TICKET.TIME_OPENED), To_days(NOW()) - To_days(HD_TICKET.TIME_OPENED) - 1), 'd ', Date_format(Addtime("2000-01-01 00:00:00", Sec_to_time(Time_to_sec(NOW()) - Time_to_sec(HD_TICKET.TIME_OPENED))), '%kh %im')) AS TIME_OPEN,
      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(OWN.FULL_NAME, ' 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
      LEFT JOIN USER OWN
        ON HD_TICKET.OWNER_ID = OWN.ID
      JOIN HD_QUEUE
        ON HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID WHERE HD_STATUS.STATE = 'Open' OR HD_STATUS.STATE = 'Stalled'
AND OWN.FULL_NAME IN ( 'David Andreason', 'Tim Rowe','Darrell Dubois','Devon Rylski','Edward Abbott','Joe Vermeulen','Scott Legros')
ORDER  BY OWNER_NAME,
         HD_PRIORITY.ORDINAL,
         HD_CATEGORY.ORDINAL,
         HD_STATUS.ORDINAL,
         HD_IMPACT.ORDINAL

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

Is this what your looking for?

 

SELECT HD_TICKET.ID, 

       HD_QUEUE.NAME, 

       HD_TICKET.TITLE, 

       HD_TICKET.DUE_DATE, 

       Concat(IF(Time_to_sec(NOW()) >= Time_to_sec(HD_TICKET.TIME_OPENED), To_days(NOW()) - To_days(HD_TICKET.TIME_OPENED), To_days(NOW()) - To_days(HD_TICKET.TIME_OPENED) - 1), 'd ', Date_format(Addtime("2000-01-01 00:00:00", Sec_to_time(Time_to_sec(NOW()) - Time_to_sec(HD_TICKET.TIME_OPENED))), '%kh %im')) AS TIME_OPEN,

       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(OWN.FULL_NAME, ' 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 

       LEFT JOIN USER OWN 

         ON HD_TICKET.OWNER_ID = OWN.ID 

       JOIN HD_QUEUE 

         ON HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID 

WHERE  ( HD_STATUS.STATE = 'Open' 

          OR HD_STATUS.STATE = 'Stalled' ) 

   AND OWN.FULL_NAME IN ( 'David Andreason', 'Tim Rowe', 'Darrell Dubois', 'Devon Rylski', 

                          'Edward Abbott', 'Joe Vermeulen', 'Scott Legros' ) 

   AND HD_TICKET.CREATED > DATE_SUB(Current_date(), INTERVAL 7 DAY) 

ORDER  BY OWNER_NAME, 

          HD_PRIORITY.ORDINAL, 

          HD_CATEGORY.ORDINAL, 

          HD_STATUS.ORDINAL, 

          HD_IMPACT.ORDINAL 
Answered 04/11/2012 by: dchristian
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity