I'd like a to create a report that will return tickets that have no hours entered for work.

There's a built in report called "Work Report last 31 days by person" which is how I'd like it to look but to show only tickets that don't have any hours entered.
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
The format of this report is a little different because the returned data doesn't match the report you cited, but this query should get you what you need:
select T.CREATED, CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET,
(SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE HD_WORK.HD_TICKET_ID = T.ID) AS HOURSWORKED
from HD_TICKET T
where T.CREATED > DATE_SUB(NOW(), INTERVAL 31 DAY)
HAVING (HOURSWORKED is null or HOURSWORKED = 0)
order by TICKET

Answered 10/13/2015 by: chucksteel
Red Belt

  • Thank you! I send you 3 virtual beers.

    Not critical but can the full name of the person who worked on the ticket be included and sorted by it?

    I tried to combine an existing SQL query with your but I'm getting syntax errors
    • Do you want the ticket submitter or the owner? If you are reporting on tickets with zero hours worked, then technically no one has worked on it, yet.
      • We want to track who has worked on the ticket but didn't enter hours so honestly if it isn't hard to do, both owner and submitter would be perfect. Otherwise if it's annoying to return both, just the owner is good.
Please log in to comment

Answers

0
This report will show work entered on a ticket with the adjustment hours is zero. This is a little different than showing tickets with zero hours.
select T.CREATED, W.START as "WorkStarted", CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET,
SUBMITTER.FULL_NAME as "Submitter",
OWNER.FULL_NAME as "Owner",
WORKER.FULL_NAME as "Worker"
from HD_TICKET T
JOIN HD_WORK W on W.HD_TICKET_ID = T.ID
JOIN USER WORKER on WORKER.ID = W.USER_ID
JOIN USER SUBMITTER ON SUBMITTER.ID = T.SUBMITTER_ID
JOIN USER OWNER ON OWNER.ID = T.OWNER_ID
where  W.ADJUSTMENT_HOURS = 0
and T.CREATED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by Worker, TICKET

Answered 10/19/2015 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share