/build/static/layout/Breadcrumb_cap_w.png

Kace report total work on parent and child tickets

Hi guys,

I have been beating my head against this for a few days now and just don't know SQL well enough to figure it out. I want a report that will give me a total of the hours worked on a parent and its child tickets as we will be using these to track projects. Currently I am close useing this query.

 SELECT
     T.`ID` AS T_ID,
(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 FROM `HD_WORK` W INNER JOIN `HD_TICKET` T ON W.`HD_TICKET_ID` = T.`ID` WHERE W.HD_TICKET_ID = T.ID AND  (T.PARENT_ID > 0
  OR T.IS_PARENT = 1)   ) as T_TITLE,
 
 If (T.`PARENT_ID` = 0 AND T.`IS_PARENT` = 1, T.`ID`, T.`PARENT_ID`)  AS T_PARENT_ID,
       T.`IS_PARENT` AS T_IS_PARENT,
     W.`ADJUSTMENT_HOURS` AS HD_WORK_ADJUSTMENT_HOURS,
     W.`HD_TICKET_ID` AS W_HD_TICKET_ID,
     W.`START` AS W_START,
     W.`STOP` AS W_STOP,
    format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2) as HOURS_WORKED
FROM
     `HD_WORK` W INNER JOIN `HD_TICKET` T ON W.`HD_TICKET_ID` = T.`ID`
WHERE
     (T.PARENT_ID > 0
  OR T.IS_PARENT = 1) 

 

This returns everything I want, however the subquery that gets the total hours is giving me the total hours for all projects when I just want it to be for the one set of hours for the parent. Any ideas?


3 Comments   [ + ] Show comments
  • 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 10 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 10 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

Answers (0)

Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ