Hey all,

Per my usual situation I have been pounding my head on my desk trying to figure out what I'm doing wrong with my SQL reports. So I have decided to turn to the SQL Gurus of the IT Ninja community for help.

Here is the situation. I have two reports; one that pulls number of tickets and hours worked based on Owner, and the other pulls number of tickets and hours worked based on a custom field we made called Type. The issue is that the sum of ticket and hours worked are different per report. It appears that my Summary by Owner ticket is pulling the correct number of tickets. Any ideas would be most helpful. Thanks!
 
Summary by Owner report:
Select 
  ORG1.USER.FULL_NAME As TECHNICIAN,
  Count(DISTINCT ORG1.HD_TICKET.ID) As TICKETS,
  Sum(ROUND((Time_To_Sec(Time(ORG1.HD_WORK.STOP)) -
  Time_To_Sec(Time(ORG1.HD_WORK.START))) / 3600.0 +
  ORG1.HD_WORK.ADJUSTMENT_HOURS, 2)) As TOTAL_HOURS_WORKED
From
  ORG1.HD_TICKET Inner Join
  ORG1.HD_WORK On ORG1.HD_WORK.HD_TICKET_ID = ORG1.HD_TICKET.ID Inner Join
  ORG1.USER On ORG1.USER.ID = ORG1.HD_WORK.USER_ID
Where
  ORG1.HD_TICKET.HD_QUEUE_ID = 1 And
  isnull(ORG1.HD_WORK.VOIDED_BY) And
 ((date(HD_TICKET.TIME_CLOSED)  >= date_add( date_sub( curdate(), interval dayofyear(curdate())-1 day), interval ((quarter(curdate())-1)*3)-(3*1) month) and date(HD_TICKET.TIME_CLOSED)  <  date_add( date_sub( curdate(), interval dayofyear(curdate())-1 day), interval ((quarter(curdate())-1)*3) month)))
Group By
  ORG1.USER.FULL_NAME

Summary by Type:
Select 
  ORG1.HD_TICKET.CUSTOM_FIELD_VALUE5 As TYPE,
  Count(DISTINCT ORG1.HD_TICKET.ID) As TICKETS,
  Sum(ROUND((Time_To_Sec(Time(ORG1.HD_WORK.STOP)) -
  Time_To_Sec(Time(ORG1.HD_WORK.START))) / 3600.0 +
  ORG1.HD_WORK.ADJUSTMENT_HOURS, 2)) As TOTAL_HOURS_WORKED
From
  ORG1.HD_TICKET Inner Join
  ORG1.HD_WORK On ORG1.HD_WORK.HD_TICKET_ID = ORG1.HD_TICKET.ID
Where
  ORG1.HD_TICKET.HD_QUEUE_ID = 1 And
  isnull(ORG1.HD_WORK.VOIDED_BY) And
 ((date(HD_TICKET.TIME_CLOSED)  >= date_add( date_sub( curdate(), interval dayofyear(curdate())-1 day), interval ((quarter(curdate())-1)*3)-(3*1) month) and date(HD_TICKET.TIME_CLOSED)  <  date_add( date_sub( curdate(), interval dayofyear(curdate())-1 day), interval ((quarter(curdate())-1)*3) month)))
Group By
  ORG1.HD_TICKET.CUSTOM_FIELD_VALUE5
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
Is it possible that one report is not showing tickets as there is no Type associated with the tickets? From your Queries it seems the only difference is in the join in the From statement. That would suggest that some data may be missing if there is no data to join?

I would call off a detailed report of all tickets in the time period and include columns for Technician and Type, to see if the error can be seen
Answered 01/26/2016 by: Hobbsy
Red Belt

  • Thanks I'll give it a try. I spent a good number of hours playing with the From statement and wasn't able to make anything come from it, pun intended. I'll let you know what I figure out. Do you think it could have something do to with the count or the work? I ran into issues when I first made the Summary by Owner ticket. It was counting each work entry so I had to add the distinct. I wonder if something like that is happening.
  • So I finally got around to checking and every ticket has a type associated with it. It's actually a field that gets auto populated. I thought maybe I hadn't turned on the auto population before the time period or some tickets were still out there from before I set it to auto populate, but alas no luck.
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share