/build/static/layout/Breadcrumb_cap_w.png

Report that returns tickets with 0 work hours

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.

0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: chucksteel 8 years ago
Red Belt
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

Posted by: chucksteel 8 years ago
Red Belt
0

Top Answer

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


Comments:
  • 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 - vitalym 8 years ago
    • 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. - chucksteel 8 years ago
      • 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. - vitalym 8 years ago
 
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