4 Replies Latest reply on May 14, 2018 2:56 PM by Andrew Klein

    Calculate datediff between inspection times for each site

    Andrew Klein

      Hello,

       

      The challenge I have is how to calculate the difference between inspection times where the inspection has occurred at the same site.

      The data I have is site number, inspection date, and inspection start time. The data I receive is not sorted in any order.

       

      I can combine the date and time columns into one data column using the below code

       

       

      DATETIME(STR([Insp Date]) + " "

                      + STR(DATEPART('hour',[Insp Time]))+ ":"

                      + STR(DATEPART('minute',[Insp Time]))+ ":"

                      + STR(DATEPART('second',[Insp Time])))

       

       

       

      The difficulty I’m having is how to calculate the difference in minutes between each sites inspections.

       

      A sample data set is copied below:

       

       

      Site_NO

      INSP DATE

      INSP TIME

      A14899

      1/04/2018

      9:30:41

      A14899

      1/04/2018

      11:40:33

      A55799

      1/04/2018

      7:42:46

      A14899

      1/04/2018

      8:17:59

      A55799

      1/04/2018

      11:22:15

      S01499

      1/04/2018

      9:51:47

      S01499

      1/04/2018

      11:06:42

      S01499

      1/04/2018

      15:32:07

      S01499

      2/04/2018

      16:30:32

      S01699

      1/04/2018

      7:28:01

      S01699

      1/04/2018

      5:34:10

      A55799

      1/04/2018

      16:51:49

      A14899

      1/04/2018

      16:07:59

      A14899

      1/04/2018

      8:16:33

      A55799

      1/04/2018

      14:47:42

       

      The desired output I need is in the final column below:

       

      Site_NO

      INSP DATE

      INSP TIME

      DESIRED OUTPUT:

      Time since previous inspection

      A14899

      1/04/2018

      8:16:33

       

      A14899

      1/04/2018

      8:17:59

      0:01:26

      A14899

      1/04/2018

      9:30:41

      1:12:42

      A14899

      1/04/2018

      11:40:33

      2:09:52

      A14899

      1/04/2018

      16:07:59

      4:27:26

      A55799

      1/04/2018

      7:42:46

       

      A55799

      1/04/2018

      11:22:15

      3:39:29

      A55799

      1/04/2018

      14:47:42

      3:25:27

      A55799

      1/04/2018

      16:51:49

      2:04:07

      S01499

      1/04/2018

      9:51:47

       

      S01499

      1/04/2018

      11:06:42

      1:14:55

      S01499

      1/04/2018

      15:32:07

      4:25:25

      S01499

      2/04/2018

      16:30:32

      24:58:25

      S01699

      1/04/2018

      5:34:10

       

      S01699

      1/04/2018

      7:28:01

      1:53:51

       

       

      Thanks in advance for your assistance.

      Andrew