I found the following SQL statement in a different post, but I need some help tweaking it show that it will display all of the tickets in KACE even if they don't have any work. Can somebody help me get the correct join syntax. Here is the SQL:


select T.ID,
T.TITLE,
T.CUSTOM_FIELD_VALUE0 as Type,
(select FULL_NAME from USER where T.SUBMITTER_ID = USER.ID) as Submitter,
TOTAL_HOURS_WORKED as Total_Hours

from (HD_WORK W, HD_TICKET T, USER U)

JOIN (select T.ID,SUM(
ROUND((time_to_sec(time(stop)) - time_to_sec(time(start)))/3600.0 + ADJUSTMENT_HOURS, 2)) as TOTAL_HOURS_WORKED
from (HD_WORK W, HD_TICKET T, USER U)
where W.HD_TICKET_ID = T.ID
and isnull(W.VOIDED_BY)
and W.USER_ID = U.ID
and W.STOP > DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY T.ID) TOTAL ON TOTAL.ID=T.ID
where W.HD_TICKET_ID = T.ID
and isnull(W.VOIDED_BY)
and W.USER_ID = U.ID
and W.STOP > DATE_SUB(NOW(), INTERVAL 12 MONTH)
group by T.ID
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
you must mean this thread:
http://itninja.com/question/adobe-reader-repair-script0&mpage=1&key=HD_WORK𔁓

If you scroll down that thread it you'll find several rewrites that do just what you requested. note that you might want to change some of the fields and the HD_QUEUE_ID value
Answered 02/16/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Gerald,

Thanks for the reply. I've been working to bring in the total hours for each ticket and honestly haven't had any luck. Can I use the above SQL that I initially posted with a left join in it to try and pull all tickets even if they don't have a work entry?
Answered 02/20/2012 by: rayjacobs
Yellow Belt

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