Report to show Hours for each Technician
I am looking to create or modify an excisting report that will show Department (Custom Field), Ticket, Date Work Entered, Technician Name, Hours Worked for last x amount of days. We currently have a report "Work Report last 31 days by person" which works ok, but is lacking the Department field.
I do not know SQL and not sure how to modify to obtain Department field or change to reflect the last 14 days or so.
Example for
Work Report last 31 days by person
Description: Reports all people who logged work during the last 31 days. Display by person, then ticket and time.
select W.STOP as DATE, CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET, U.FULL_NAME, substring(W.NOTE,1,1200) as NOTE,
format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2) as HOURS_WORKED
from (HD_WORK W, HD_TICKET T, USER U)
where W.HD_TICKET_ID = T.ID
and isnull(W.VOIDED_BY)
and W.USER_ID = U.ID
and W.STOP > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by U.FULL_NAME, TICKET, W.STOP
I would like to display this by Department, Person / Technician, Date and Time.
It should also include all Queues
Other info if needed. We are using K1000 6.2
Any help would be greatly appreciated.
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
8 years ago
To add the department you will need to know the custom field number and then subtract one from it. This is because the numbering starts at zero in the database, so custom field 1 is stored in HD_TICKET.CUSTOM_FIELD_VALUE0. Once you know that information you can add the field to the select stanza of your statement. If you want department first, then place HD_TICKET.CUSTOM_FIELD_VALUE0, after the word select, if you want it someplace else, place it accordingly. Fields being selected are in a comma separated list after the word select and before the word from.
To change the timeframe of this report modify the W.STOP > DATE_SUB(NOW(), INTERVAL 31 DAY) line. Change 31 DAY to 14 DAY to report on the past 14 days.
This query doesn't specify a specific queue so it should already report on all queues.
Comments:
-
When I add HD_TICKET.CUSTOM_FIELD_VALUE2 (2 is my field minus 1) I get the red error dialog box when I try to save. Changing to 14 days works fine.
Example of edited sql:
select HD_TICKET.CUSTOM_FIELD_VALUE2, W.STOP as DATE, CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET, U.FULL_NAME, substring(W.NOTE,1,1200) as NOTE,
format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2) as HOURS_WORKED
from (HD_WORK W, HD_TICKET T, USER U)
where W.HD_TICKET_ID = T.ID
and isnull(W.VOIDED_BY)
and W.USER_ID = U.ID
and W.STOP > DATE_SUB(NOW(), INTERVAL 14 DAY)
order by U.FULL_NAME, TICKET, W.STOP
mysql error: [1054: Unknown column 'HD_TICKET.CUSTOM_FIELD_VALUE2' in 'field list'] in EXECUTE( "select HD_TICKET.CUSTOM_FIELD_VALUE2, W.STOP as DATE, CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET, U.FULL_NAME, substring(W.NOTE,1,1200) as NOTE, format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2) as HOURS_WORKED from (HD_WORK W, HD_TICKET T, USER U) where W.HD_TICKET_ID = T.ID and isnull(W.VOIDED_BY) and W.USER_ID = U.ID and W.STOP > DATE_SUB(NOW(), INTERVAL 14 DAY) order by U.FULL_NAME, TICKET, W.STOP LIMIT 0")
Here is an example of another report with my department entry that does work, minus hours worked and such done with wizard and only one que and not time frame:
SELECT HD_TICKET.CUSTOM_FIELD_VALUE2 AS DEPARTMENT, HD_TICKET.ID, O.FULL_NAME AS OWNER_NAME, HD_TICKET.TITLE, HD_TICKET.CUSTOM_FIELD_VALUE1 AS AFFECTED_USER FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) ORDER BY DEPARTMENT, OWNER_NAME - wessdf 8 years ago-
HD_TICKET is aliased to T (missed that earlier), so you need to use T.CUSTOM_FIELD_VALUE2. - chucksteel 8 years ago
-
BINGO, thank you so much, that did the trick. I really don't know anything about sql and you saved me big time.
I'm sure I will have more questions as I modify this once again to reflect pertinent data a formatting lol
Thanks again!!! - wessdf 8 years ago -
Chuck, since you were so helpful could you possibly show me how to modify my sql below so that it would show just a specified tech. Currently I show all of our techs and would like to create a report for each if desired to fill our time cards out :(
select T.CUSTOM_FIELD_VALUE2 AS DEPARTMENT, W.STOP as DATE, CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET, U.FULL_NAME,
format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2) as HOURS_WORKED
from (HD_WORK W, HD_TICKET T, USER U)
where W.HD_TICKET_ID = T.ID
and isnull(W.VOIDED_BY)
and W.USER_ID = U.ID
and W.STOP > DATE_SUB(NOW(), INTERVAL 7 DAY)
order by U.FULL_NAME, W.STOP, T.CUSTOM_FIELD_VALUE2 - wessdf 8 years ago -
After this line:
and W.STOP > DATE_SUB(NOW(), INTERVAL 7 DAY)
Add
and U.USER_NAME = "username"
to limit the results to a specific username. - chucksteel 8 years ago -
Once again, thank you very much, works perfect! - wessdf 8 years ago
