/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

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