Kace - Service Desk Report for Quarterly Hours Worked for a Department
Hello All,
I'm new to SQL queries and was messing around with a query i found searching for "Kace Hours Worked". Basically i was trying to pull data from the HD_Ticket table and join HD_Work so i can get data to return by Ticket ID, Ticket Title, Date, Submitter, Owner, and Department. We need a way to record quarterly billing hours for a certain department to get reimbursement. I tried pulling the info i needed from the Kace Report Wizard but had no luck finding work hours to associate with it.
This is the query i was messing with:
SELECT W.STOP as Date, W.HD_TICKET_ID as TicketID, SUM(W.ADJUSTMENT_HOURS) as HoursWorked, USER.FULL_NAME as Owner, HD_TICKET.CUSTOM_FIELD_VALUE0 as Department
FROM ORG1.HD_TICKET W
JOIN HD_Work on W.Work_ID = Work.ID
WHERE W.STOP > DATE_SUB(NOW(), INTERVAL 91.25 DAY)
and HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Department Name'
GROUP BY W.HD_TICKET_ID
Any Help would be greatly appreciated.
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
6 years ago
It looks like you got your table aliases and join statement confused. This line:
FROM ORG1.HD_TICKET W
is saying get tickets from the ORG1.HD_TICKET table, but call the table W.
In the next line you are making a relationship to another table:
JOIN HD_WORK on W.WORK_ID = WORK.ID
but the database doesn't know what some of these things are. The english translation of this line would be something like "Get columns from this other table HD_WORK, pick the rows where the WORK_ID column in the table W is equal to the ID column in the table WORK". Unfortunately, the table W (which is actually HD_TICKET) doesn't contain a column named HD_WORK, plus, the database doesn't have a table named WORK for the second part of the relationship.
What you really want to say with these two lines is probably this:
FROM HD_TICKET T
JOIN HD_WORK W on W.HD_TICKET_ID = T.ID
I use T as the alias for the ticket table, and W as the alias for the work table, that's a little more clear. Plus the HD_WORK table has a column for HD_TICKET_ID that holds the ticket ID.
You are also requesting data from the USER table, but the database doesn't know how to get information from that table, because there isn't a relationship defined, so you'll need to add a join statement for that:
JOIN USER on USER.ID = T.OWNER_ID
Your query should now look like this:
SELECT W.STOP as Date, W.HD_TICKET_ID as TicketID, SUM(W.ADJUSTMENT_HOURS) as HoursWorked,
USER.FULL_NAME as Owner, T.CUSTOM_FIELD_VALUE0 as Department
FROM ORG1.HD_TICKET T
JOIN HD_WORK W on W.HD_TICKET_ID = T.ID
JOIN USER on USER.ID = T.OWNER_ID
WHERE W.STOP > DATE_SUB(NOW(), INTERVAL 91.25 DAY)
and T.CUSTOM_FIELD_VALUE0 = 'Department Name'
GROUP BY W.HD_TICKET_ID
I would also recommend using a different method to report by quarter. Changing the WHERE line to this:
WHERE QUARTER(W.STOP) = QUARTER(NOW()) and YEAR(W.STOP) = YEAR(NOW())
will show the tickets for the current quarter. You can then schedule this report to run on the last day of the quarter and be done with it.
Comments:
-
Thanks I appreciate your help! I honestly didn't understand what the "W" did on the line.
FROM ORG1.HD_TICKET W
So that makes much more sense. I will also schedule a report to streamline the process. - RSchott 6 years ago
