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:
Please log in to answer
Posted by:
chucksteel
9 years ago
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
9 years ago
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 9 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 9 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 9 years ago
