I created a report through the wizard and when I went in to edit the SQL to include a simple datediff in the select statement to get the difference between the open date and closed date. It's giving me a syntax error. Am I missing something? All the help would be appreciated. Thanks!


SELECT HD_CATEGORY.NAME AS CATEGORY, HD_TICKET.CREATED AS HD_TICKET_CREATED,
S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.TIME_CLOSED AS HD_TICKET_TIME_CLOSED,
HD_TICKET.TIME_OPENED AS HD_TICKET_TIME_OPENED, HD_TICKET.CUSTOM_FIELD_VALUE0 AS HD_TICKET_CUSTOM_FIELD_VALUE0,
DATEDIFF(day, HD_TICKET.TIME_OPENED, HD_TICKET.TIME_CLOSED) AS DURATION
FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND ( HD_CATEGORY.NAME LIKE '%Trade Pending%'
AND (DATE(HD_TICKET.TIME_OPENED)> DATE_SUB(NOW(), INTERVAL 7 day)
AND DATE(HD_TICKET.TIME_OPENED)<= NOW())) ORDER BY HD_TICKET.CREATED asc
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
DATEDIFF only has two input variables. Get rid of that 'day' you've got at the beginning of the function. DATEDIFF shows you the difference in days by default.


SELECT HD_CATEGORY.NAME AS CATEGORY, HD_TICKET.CREATED AS HD_TICKET_CREATED,
S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.TIME_CLOSED AS HD_TICKET_TIME_CLOSED,
HD_TICKET.TIME_OPENED AS HD_TICKET_TIME_OPENED, HD_TICKET.CUSTOM_FIELD_VALUE0 AS HD_TICKET_CUSTOM_FIELD_VALUE0,
DATEDIFF(HD_TICKET.TIME_OPENED, HD_TICKET.TIME_CLOSED) AS DURATION
FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND ( HD_CATEGORY.NAME LIKE '%Trade Pending%'
AND (DATE(HD_TICKET.TIME_OPENED)> DATE_SUB(NOW(), INTERVAL 7 day)
AND DATE(HD_TICKET.TIME_OPENED)<= NOW())) ORDER BY HD_TICKET.CREATED asc
Answered 08/10/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Thanks for the quick response airwolf. How do I get my duration in H:MM:SS format? When it ran, it return 0 value. Is that the day?
Answered 08/10/2010 by: kaneda0149
Orange Senior Belt

Please log in to comment
0
Well, what do you want that field to show? Using DATEDIFF with TIME_OPENED and TIME_CLOSED, you'll only get a value (non-NULL) if both of those times exist. So, only closed tickets will show you a valid DATEDIFF. If you only want to show opened or closed tickets, you'll need to add the status to your query either way.

At this point, I need to know what you want your report to show you.
Answered 08/10/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Want the new field to show the the hour, minute and seconds from the time the Ticket was Opened to the time the Ticket was Closed.
Answered 08/10/2010 by: kaneda0149
Orange Senior Belt

Please log in to comment
0
Then, by definition, you only want to see closed tickets. Also, you want TIMEDIFF, not DATEDIFF. Try this:


SELECT HD_CATEGORY.NAME AS CATEGORY, HD_TICKET.CREATED AS HD_TICKET_CREATED,
S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.TIME_CLOSED AS HD_TICKET_TIME_CLOSED,
HD_TICKET.TIME_OPENED AS HD_TICKET_TIME_OPENED, HD_TICKET.CUSTOM_FIELD_VALUE0 AS HD_TICKET_CUSTOM_FIELD_VALUE0,
TIMEDIFF(HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED) AS DURATION
FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE (HD_STATUS.STATE like 'CLOSED') AND (HD_TICKET.HD_QUEUE_ID = 3) AND ( HD_CATEGORY.NAME LIKE '%Trade Pending%'
AND (DATE(HD_TICKET.TIME_OPENED)> DATE_SUB(NOW(), INTERVAL 7 day)
AND DATE(HD_TICKET.TIME_OPENED)<= NOW())) ORDER BY HD_TICKET.CREATED asc
Answered 08/10/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Airwolf, that was SaWeeet! One last item, can it show without the actual date? Also why is the SQL statement in this different from standard SQL?
Answered 08/10/2010 by: kaneda0149
Orange Senior Belt

Please log in to comment
0
can it show without the actual date?
I'm not sure what you mean...

Also why is the SQL statement in this different from standard SQL?
I don't know what you mean by "standard SQL". The KBOX uses MySQL, so the syntax is going to be different than something like SQLite, MS SQL, or Oracle.
Answered 08/10/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Sorry new Duration value returned was in the format of "01/01/1970 00:08:25", just want to see "00:08:25". Sorry I'm used to using MS SQL and it threw me off, did not realize it was using MySQL. Thanks for explanation ;o)
Answered 08/10/2010 by: kaneda0149
Orange Senior Belt

Please log in to comment
0
Duration should already return as HH:MM:SS. TIMEDIFF returns a result in HH:MM:SS. You can try wrapping the TIMEDIFF function with TIME(), but that is redundant. Your CREATED, CLOSED, and OPENED times will all be shown in YYYY-MM-DD HH:MM:SS format.
Answered 08/10/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Makes sense, thanks for your help!
Answered 08/10/2010 by: kaneda0149
Orange Senior Belt

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