3 Replies Latest reply on Sep 20, 2016 6:39 AM by David Li

    Using DATEDIFF with multiple rows

    Marquis Calmes

      As I'm struggling to come up with a good title for the question, this may be a long shot.   I'm working off data from a SQL database for help desk ticketing system.

       

      The basic table structure is as follows:

      • Service Request - This contains the following fields among others:
        • SR Service RecID - This is essentially the ticket number
        • Date Entered UTC - This is when the ticket was created
      • Time Entry - This contains time entries. The important thing is that there can be multiple time entries for each service request
        • SR Service RecID - (This is used to join with the Service Request table)
        • Time Start UTC - This is when a time entry starts

       

      Obviously there are other fields and tables but I think this covers the most salient info.   The goal is to calculate how long it is taking for us to respond to new tickets on average say per week.  So of tickets created in Week X, what was the average difference between the "Date Entered UTC" and the earliest of the "Time Start UTC" entries.

       

       

      Below is the calculated field formula I have come up with so far.

      IF

      DATEDIFF('hour',attr([Date Entered UTC]),(min([Time Start UTC]))) < 0

      THEN

      1

      ELSE

      DATEDIFF('hour',attr([Date Entered UTC]),(min([Time Start UTC])))

      END

      I added the "If <0" logic because sometimes a ticket is created after the first time entry.  I didn't want to be averaging in a negative number.

      I had to use the ATTR function on the Date Entered UTC to get rid of the aggregate mismatch error.


      If I build a worksheet table with the SR Service it appears to work. For each Service Rec ID it is calculating properly. The problem is displaying an average over time.  If I build a worksheet using the "Date Entered UTC" the data goes all over the place with most of it becoming blank and I can't figure out a way to get it to average.