/build/static/layout/Breadcrumb_cap_w.png

HD_WORK is pulling deleted entries.

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

0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: POB Technology 8 years ago
Senior White Belt
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.
 
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