What do I add to the code below to create a subject field in the report?

SELECT HD_TICKET.ID, HD_TICKET.CREATED, S.FULL_NAME AS SUBMITTER_NAME, O.FULL_NAME AS OWNER_NAME, HD_TICKET.DUE_DATE, HD_STATUS.NAME AS STATUS_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) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ((HD_STATUS.NAME != 'Closed') AND ((TIMESTAMP(HD_TICKET.CREATED) > NOW() OR TIMESTAMP(HD_TICKET.CREATED) <= DATE_SUB(NOW(),INTERVAL 14 DAY))))  ORDER BY ID
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
Hello,

something like that?

SELECT HD_TICKET.ID,
       HD_TICKET.CREATED,
       S.FULL_NAME AS SUBMITTER_NAME,
       O.FULL_NAME AS OWNER_NAME,
       HD_TICKET.SUMMARY,
       HD_TICKET.DUE_DATE,
       HD_STATUS.NAME AS STATUS_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)
 WHERE     (HD_TICKET.HD_QUEUE_ID = 1)
       AND (    (HD_STATUS.NAME != 'Closed')
            AND ((   TIMESTAMP(HD_TICKET.CREATED) > NOW()
                  OR TIMESTAMP(HD_TICKET.CREATED) <=
                        DATE_SUB(NOW(), INTERVAL 14 DAY))))
ORDER BY ID


Kind regards,

Marco - StockTrader

Answered 12/17/2015 by: StockTrader
Red Belt

  • Yes that makes sense! What would be the code to have the report sort by technician. I would rather use the wizard because it has more options but don't know what to put in the filters to have it generate a report of new and open tickets 14 days or older.
    • Instead of ORDER BY ID you can use ORDER BY OWNER_NAME...
      Anyway you can use the wizard as well.
      If in the filters section of the wizard you select a date field (like Created for example) you can use the operator "is within last" and specify 15 days.
      Kind regards,
      StockTrader - Marco
      • StockTrader, I did as you indicated and it worked like a charm. Thank you so much!
Please log in to comment

Answers

Answer this question or Comment on this question for clarity

Share