/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Kace - Service Desk Report for Quarterly Hours Worked for a Department

02/06/2018 1303 views
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

Comments


All Answers

2
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.

Answered 02/07/2018 by: chucksteel
Red Belt

  • 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.

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