/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Need help with report showing time worked on each ticket opened in last 7 days

01/22/2019 230 views

I am trying to create a report that shows us the amount of time that was spent on each ticket in the last 7 days.  My field for the time spent on a ticket is listed as HD_TICKET.CUSTOM_FIELD_VALUE4 but I am not sure what I have wrong in my SQL statement as it errors when I try to save the statement.

SELECT HD_TICKET.CREATED, HD_TICKET.ID, O.USER_NAME AS OWNER_USER_NAME, HD_TICKET.CUSTOM_FIELD_VALUE4, HD_TICKET.TITLE  FROM HD_TICKET  LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 5) AND ((HD_TICKET.CUSTOM_FIELD_VALUE4 != ''))  
WHERE T.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER BY CREATED, ID


The error I get is below.

"mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE T.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER BY CREATED, ID LIMIT 0' at line 2] in EXECUTE(\n"SELECT HD_TICKET.CREATED, HD_TICKET.ID, O.USER_NAME AS OWNER_USER_NAME, HD_TICKET.CUSTOM_FIELD_VALUE4, HD_TICKET.TITLE FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 5) AND ((HD_TICKET.CUSTOM_FIELD_VALUE4 != '')) \nWHERE T.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER BY CREATED, ID LIMIT 0")\n"


Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0

You have two where clauses "WHERE (HD_TICKET.HD_QUEUE_ID = 5) AND ((HD_TICKET.CUSTOM_FIELD_VALUE4 != ''))

WHERE T.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER BY CREATED, ID"

Change the second WHERE to AND.

Answered 01/23/2019 by: chucksteel
Red Belt

  • Thank you for seeing what I had missed. Unfortunately the SQL statement did not do what I wanted it to do, but I did finally figure out, and did not make the same mistake I made previously by making sure I used the AND instead of WHERE on the second part.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ