KACE Product Support

I'm having some problems mentally parsing your query but I don't see where you join child tickets to the parent in order to get the data from the child tickets entries in the HD_WORK table. - chucksteel 11 years ago

Hi Chuck. I took the base for this script from the following posts and have just been tweaking it to try to fix my needs

http://www.itninja.com/question/how-can-i-add-a-work-total-column-to-the-work-report

I believe the child/parents are being joined as part of the:

FROM
`HD_WORK` W INNER JOIN `HD_TICKET` T ON W.`HD_TICKET_ID` = T.`ID`

This seems to add the time from ALL tickets, but that is stopped by the where and groupby clause which is what filters it down to just the tickets for that part of the query

I have tweaked it a bit this morning and slimmed it down so it should be easier to follow

NEW QUERY
"
SELECT
CONCAT(" Parent Ticket: " , If (T.`PARENT_ID` = 0 AND T.`IS_PARENT` = 1, T.`ID`, T.`PARENT_ID`),
" Hours Worked: " , SUM(format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2))) AS T_TITLE,

If (T.`PARENT_ID` = 0 AND T.`IS_PARENT` = 1, T.`ID`, T.`PARENT_ID`) AS ProjectTicketID,
T.ID AS "Ticket ID"
FROM

`HD_WORK` W INNER JOIN `HD_TICKET` T ON W.`HD_TICKET_ID` = T.`ID` JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = T.ID)

WHERE
(T.PARENT_ID > 0
OR T.IS_PARENT = 1)
Group By If (T.`PARENT_ID` = 0 AND T.`IS_PARENT` = 1, T.`ID`, T.`PARENT_ID`)
"
Break on T_TITLE

This gives me the following output:

# ProjectTicketID Ticket ID
1 of T Title: Parent Ticket: 155 Hours Worked: 7
1 155 156

1 of T Title: Parent Ticket: 157 Hours Worked: 27
2 157 157

The math is now working on the totals but it is not showing both the parent and the child tickets in the rows. It should look like this (I dont have the query in for the part in brackets at the moment but if both ticket rows were displaying I could add it in). Maybe I am just going about this all wrong :(

# ProjectTicketID Ticket ID
1 of T Title: Parent Ticket: 155 Hours Worked: 7
1 155 155 (hours worked on just this ticket =2)
1 155 156 (hours worked on just this ticket =5)

1 of T Title: Parent Ticket: 157 Hours Worked: 27
2 157 157 (hours worked on just this ticket =20)
2 157 158 (hours worked on just this ticket =7) - prattk 11 years ago

Would appreciate a solution to this too. I'm at an impass for this trying to show work notes and hours worked on child calls of a parent. grouped by the parent call. was this resolved Prattk at all or did you find another route? - JCKACE 7 years ago