/build/static/layout/Breadcrumb_cap_w.png

SQL help; Reports are pulling different information

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

Answers (1)

Posted by: Hobbsy 8 years ago
Red Belt
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

Comments:
  • 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. - POB Technology 8 years ago
  • 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. - POB Technology 8 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
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