KACE Product Support Question

Report; number of tickets per owner with work.

04/03/2015 3588 views
Hey all,

I'm trying to create a few reports for my boss and I'm having a hard time. I've been scouring through report questions and I've been able to put together some of them.

What I need is a report that shows the number of Tickets per owner and the amount of work per owner. This would be a per quarter report, that would run after the quarter has ended. I thought it would be relatively simple, for a guy like me who is just now starting to learn SQL because of KACE, but I cannot get the tickets to count correctly. Instead of counting the number of tickets it is counting the number of work entries. Any and all help would be greatly appreciated. Here is my code.

Select
ORG1.USER.FULL_NAME As TECHNICIAN,
Count(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 +
From
ORG1.HD_TICKET Inner Join
ORG1.USER On ORG1.USER.ID = ORG1.HD_TICKET.OWNER_ID Inner Join
ORG1.HD_WORK On ORG1.HD_TICKET.ID = ORG1.HD_WORK.HD_TICKET_ID
Where
ORG1.HD_TICKET.HD_QUEUE_ID = 1 And
Interval DayOfYear(CurDate()) - 1 Day), Interval ((Quarter(CurDate()) - 1) *
3) - (3 * 1) Month) And
Interval DayOfYear(CurDate()) - 1 Day), Interval ((Quarter(CurDate()) - 1) *
3) Month)
Group By
ORG1.USER.FULL_NAME

Thanks Again,
Patrick

• nicely done, sometimes its the process of explaining the problem that exposes the answer. Good job on answering it.
• Very much appreciate that self-resolution. I've been over complicating things with joins and Unions trying to achieve what adding an 8 letter command has resolved. At least i've learnt about joins and unions for future

0
Okay, so it always seems inevitable that I figure out my own solution as soon as I post a question on IT Ninja. All I had to do was make the count distinct.

Count(DISTINCT ORG1.HD_TICKET.ID) As TICKETS,

Thanks to anyone who was trying to figure this out for me.

Don't be a Stranger!

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

question

Post

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