/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide Comments

Comments

Please log in to comment

Answer this question or Comment on this question for clarity

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.
Please log in to comment