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?

2 Comments   [ + ] Show Comments

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.
  • 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)
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity