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
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
Well as was inevitable from my past history with IT Ninja; I fixed my own problem again. I won't stop posting questions though, because I can't ever figure it out until I ask about it.

In case anyone stumbles upon this same issue you have to add one line of code otherwise it pulls all entries even "voided" entries.

Where
    isnull(ORG1.HD_WORK.VOIDED_BY)

Thanks everyone.
Answered 04/09/2015 by: POB Technology
Senior White Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share