1 Reply Latest reply on Dec 21, 2017 2:51 PM by rodegard

    Reporting help

    zkelley

      I need to build a report for my School Board with what seems like to me pretty basic information but I can not find how to do it. I called support and have been on hold for 60 minutes now and the girl has not been able to figure it out either.

       

      What I am looking for is:

       

      How many tickets were closed in the last 6 months.    

           Of those how many were closed withing 24 hours. 48 hours ect...

       

      Any help you can give would be appreciated.

       

      Thanks!

        • Re: Reporting help
          rodegard

          If you are comfortable with SQL, this may be useful.  The SQL compares the report_date (date the ticket was opened), to the last_update which should be the date/time stamp when the ticket was changed to a status of closed.  You could also compare first_response to last_update if you are measuring the tech's actual time.

           

          SELECT

          JOB_TICKET.JOB_TICKET_ID,

          JOB_TICKET.QUESTION_TEXT,

          rtrim(CLIENT.FIRST_NAME) + ' ' + rtrim(CLIENT.LAST_NAME) AS CLIENT,

          CLIENT.PHONE,

          rtrim(TECH.FIRST_NAME) + ' ' + rtrim(TECH.LAST_NAME) AS ASSIGNED_TO,

          PRIORITY_TYPE.PRIORITY_TYPE_NAME,

          PROBLEM_TYPE.PROBLEM_TYPE_NAME,

          STATUS_TYPE.STATUS_TYPE_NAME,

          CONVERT(VARCHAR(20),JOB_TICKET.REPORT_DATE,100) AS OPEN_DATE,

          CONVERT(VARCHAR(20),JOB_TICKET.FIRST_RESPONSE_DATE,100) AS FIRST_RESPONSE,

          CONVERT(VARCHAR(20),JOB_TICKET.LAST_UPDATED,100) AS LAST_UPDATE,

          DATEDIFF(minute,JOB_TICKET.REPORT_DATE,JOB_TICKET.LAST_UPDATED) AS DURATION

           

           

          FROM JOB_TICKET LEFT OUTER JOIN

          TECH ON TECH.CLIENT_ID = JOB_TICKET.ASSIGNED_TECH_ID LEFT OUTER JOIN

          CLIENT ON CLIENT.CLIENT_ID = JOB_TICKET.CLIENT_ID LEFT OUTER JOIN

          PROBLEM_TYPE ON PROBLEM_TYPE.PROBLEM_TYPE_ID = JOB_TICKET.PROBLEM_TYPE_ID LEFT OUTER JOIN

          PRIORITY_TYPE ON PRIORITY_TYPE.PRIORITY_TYPE_ID = JOB_TICKET.PRIORITY_TYPE_ID LEFT OUTER JOIN

          STATUS_TYPE ON STATUS_TYPE.STATUS_TYPE_ID = JOB_TICKET.STATUS_TYPE_ID

           

           

          WHERE JOB_TICKET.DELETED <> '1'

          AND JOB_TICKET.REPORT_DATE >= '20170601'

          AND STATUS_TYPE.STATUS_TYPE_NAME = 'Closed'

           

           

          ORDER BY JOB_TICKET_ID

           

          The duration column is in minutes and you could change that to hours by changing the DATEDIFF parameter from minute to hour.  Note that if you change it to hour anything under 1 hour will appear as zero.