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

Comments

Please log in to comment

Answers

1
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.

Answered 06/08/2015 by: chucksteel
Red Belt

  • 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
    • HD_TICKET is aliased to T (missed that earlier), so you need to use T.CUSTOM_FIELD_VALUE2.
      • 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!!!
      • 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
      • 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.
      • Once again, thank you very much, works perfect!
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