KACE Product Support Question
Report; number of tickets per owner with work.
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 +
ORG1.HD_WORK.ADJUSTMENT_HOURS, 2)) As TOTAL_HOURS_WORKED
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
Date(ORG1.HD_TICKET.CUSTOM_FIELD_VALUE3) >= Date_Add(Date_Sub(CurDate(),
Interval DayOfYear(CurDate()) - 1 Day), Interval ((Quarter(CurDate()) - 1) *
3) - (3 * 1) Month) And
Date(ORG1.HD_TICKET.CUSTOM_FIELD_VALUE3) < Date_Add(Date_Sub(CurDate(),
Interval DayOfYear(CurDate()) - 1 Day), Interval ((Quarter(CurDate()) - 1) *
3) Month)
Group By
ORG1.USER.FULL_NAME
Thanks Again,
Patrick
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 +
ORG1.HD_WORK.ADJUSTMENT_HOURS, 2)) As TOTAL_HOURS_WORKED
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
Date(ORG1.HD_TICKET.CUSTOM_FIELD_VALUE3) >= Date_Add(Date_Sub(CurDate(),
Interval DayOfYear(CurDate()) - 1 Day), Interval ((Quarter(CurDate()) - 1) *
3) - (3 * 1) Month) And
Date(ORG1.HD_TICKET.CUSTOM_FIELD_VALUE3) < Date_Add(Date_Sub(CurDate(),
Interval DayOfYear(CurDate()) - 1 Day), Interval ((Quarter(CurDate()) - 1) *
3) Month)
Group By
ORG1.USER.FULL_NAME
Thanks Again,
Patrick
Answer Summary:
2 Comments
[ + ] Show comments
All Answers
Please log in to answer
Comments