Hours Worked (Outer Join)
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
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
Answers (2)
Please log in to answer
Posted by:
GillySpy
12 years ago
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
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
Posted by:
rayjacobs
12 years ago
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?
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?
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.