KACE Product Support

Hey all,

My helpdesk relies heavily on the add work functionality of each ticket and every once in a while one of us will accidentally forget the clock is in military time and add work. Well this generates a negative time entry so we delete that work record and recreate it with the correct time. However I'm running into an odd issue.

When I run reports that pull in the time worked for each report it is pulling in the deleted entries. Not only that, but the work notes are also pulling the deleted entries. So my time worked is displaying incorrectly. I don't know if my report query is wrong, or if I need to add something. Any help would be greatly appreciated.

-Patrick

Select
  ORG1.HD_TICKET.ID As TICKET,
  ORG1.HD_TICKET.TITLE,
  ORG1.HD_TICKET.CUSTOM_FIELD_VALUE3 As TICKET_DATE,
  ORG1.USER.FULL_NAME As OWNER,
  ORG1.HD_STATUS.NAME As STATUS,
  USER1.FULL_NAME As REQUESTER,
  USER1.LOCATION As OFFICE,
  GROUP_CONCAT(ORG1.HD_WORK.NOTE SEPARATOR '\n | \n') As DESCRIPTION,
  GROUP_CONCAT(ROUND((time_to_sec(time(stop)) - time_to_sec(time(start)))/3600.0 + ADJUSTMENT_HOURS, 2)SEPARATOR '\n | \n') As HOURS_WORKED,
  (sum(ROUND((time_to_sec(time(stop)) - time_to_sec(time(start)))/3600.0 + ADJUSTMENT_HOURS, 2))) As TOTAL_HOURS_WORKED
 
 
From
  ORG1.HD_TICKET Inner Join
  ORG1.USER On ORG1.HD_TICKET.OWNER_ID = ORG1.USER.ID Inner Join
  ORG1.HD_STATUS On ORG1.HD_TICKET.HD_STATUS_ID = ORG1.HD_STATUS.ID Inner Join
  ORG1.USER USER1 On ORG1.HD_TICKET.CUSTOM_FIELD_VALUE4 = USER1.ID Inner Join
  ORG1.HD_WORK On ORG1.HD_WORK.HD_TICKET_ID = ORG1.HD_TICKET.ID
Where
  ORG1.HD_STATUS.NAME Not Like '%closed%' And
  ORG1.HD_TICKET.HD_QUEUE_ID = 1
GROUP BY
ORG1.HD_TICKET.ID
Order By
  OFFICE,
  OWNER,
  TICKET_DATE