Hi All

Can someone assist me in an SQL script to pull the following.  This would pull from a a queue called IT Helpdesk.  The following would need to be done:

Ticket ID

Title

Time Closed

Category

Submitter User Name

Hours worked (with a total)

Would need the last 30 days for each month so far in 2015 and then one for a scheduled time reporting for the last 7 days. 

I appreciate anyones help with this.

2 Comments   [ + ] Show Comments

Comments

  • To clarify, you're basically wanting a list of all tickets that fit two types of criteria (as below), correct?

    1. One report for each month of 2015 including all tickets created that month.

    &

    2. A dynamic report that can be run at any time showing all tickets created in the last 7 days.

    Also, what other criteria would apply? Are you only wanting closed tickets? Only tickets remaining open? Etc..
  • I am guessing that you want closed calls as you need the close date. You will need to get a custom report written to give you what you want, but in the meantime below is some reporting code that will hopefully help.

    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

    You will be able to change the interval to '7 Day' for your weekly report
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity