1 Reply Latest reply on Jul 19, 2019 3:00 AM by Matt Stringham

    Work Order Audit Log - Get Next Date

    Matt Stringham
      Share This:

      Smart Reporting V 9.1.04

      I have this requirement - Customer wants to see how long a ticket sits in their queue (initially assigned to the support Group) until it is acted upon (reassigned, status change, added comment, etc - possibilities are endless)


      I have successfully been able to get the date/time the ticket enters the queue.  To do this I am using the WOI:WorkOrder_AuditLog table.  Here is my setup:

      Selected Columns:

      Table - WOI:WorkOrder

      Work Order ID



      Assigned Group

      Completed Date


      Table - WOI:WorkOrder_AuditLog

      Audit Date

      Modified Date


      The "Audit Date" selected in the report will give me the date/time when the ticket was assigned to that group.  I also do an aggregation of MAX in case the ticket gets reassigned several times.  The customer only wants the latest time it was assigned to the queue.  I have manually checked several work orders and it looks good and correct.  Here is the result (For one ticket only):

      I now add a sub query to this report, join by Work Order ID:

      select Audit date for the column - this is just showing all the dates in the audit log:

      I want the next date only 5/18 to get it, In the sub query filter I add this:

      When I refresh the report the date of 5/18 (the one I want) is not there but skips a few dates and shows some that are greater than the audit date, WHY???:


      Anyone know how to solve my problem?  I need an audit date that is the next entry after my Master Query but I've tried several things without any success.

        • 1. Re: Work Order Audit Log - Get Next Date
          Matt Stringham

          I also tried to do a CASE statement where the modified date is greater than the audit date but it comes up blank.  I'm really stuck on this.  Does anyone know how to solve How long a ticket sits before the next edit is done.  For example


          John gets assigned a ticket on 5/10 he marks it as pending 5/11 - so it sat for 1 day before he did anything


          Jane gets assigned a ticket on 5/10 and makes a comment on 5/12 - so it sat for 2 days before she did anything.


          Somehow I need to figure out after a ticket is assigned how long did it sit before the person did ANYTHING to the ticket (assigned it out to someone else, a comment, status changes, notes, etc).


          Any help or direction would be appreciated.