0 Replies Latest reply: May 8, 2012 2:51 AM by edstromp RSS

    Calculating time differences based on a group

      Hello,

       

      I have a table with about 60000 records which look something like this:

       

      Table1:

      LOAD *

      INLINE [

          Group, Start_datetime, Stop_datetime

          "A", "2012-05-01 12:00:01", "2012-05-01 12:15:11",

          "B", "2012-05-01 13:00:01", "2012-05-01 13:15:11",

          "A", "2012-05-01 16:00:01", "2012-05-01 17:13:54",

          "A", "2012-05-02 05:00:01", "2012-05-02 12:15:11",

          "B", "2012-05-02 12:00:01", "2012-05-02 13:15:11",

          "C", "2012-05-02 13:00:01", "2012-05-02 14:15:11"

      ];

       

      Now I would like to calculate the difference in time between Start_datetime and previous Start_datetime, grouped by the column Group. The result would be something like this:

       

       

      GroupStart_datetimeStop_datetimeTime since last one
      A2012-05-01  12:00:012012-05-01  12:15:11n/a
      A2012-05-01  16:00:012012-05-01  17:13:5404:00:00
      A2012-05-02  05:00:012012-05-02  12:15:1113:00:00
      B2012-05-01  13:00:012012-05-01  13:15:11n/a
      B2012-05-02  12:00:012012-05-02  13:15:1123:00:00
      C2012-05-02  13:00:012012-05-02  14:15:11n/a

       

      The next step would be to calculate the sum of all "time since last one" over a time dimension (year) and group.

       

      The problem is not to calculate the time difference. I found the functions previous() and peak() for addressing the previous line, and interval() and timestamp() to calculate the difference and display it in any format. However - I can not find out a way to group the lines by the column Group and calculate only the matching lines!

       

      If you could present me a solution or a hint thereof, I would be grateful.

       

      Peter