2 Replies Latest reply: Sep 24, 2015 5:00 AM by Lukas Amann RSS

    Split duration time into seperate records per date

    Lukas Amann

      Hello,

       

      I am trying to build a performance Indicator which should show the daily run time and the different breakdown reasons of the Machine.

      The Problem is, that the running time sometimes start a day before the endtime.  So I need to do a cut at Midnight 00:00:00 and split the duration in the correct proportion to 2 diffrent records.

      Here is the Example Data:

       

            

      Notification_IDMachineStatusStart_Date_TimeEnd_Date_TimeDuration_Sec
      112233ABCRunning10.02.2015 21:29:3911.02.2015 02:49:1419175
      112235ABCRunning11.02.2015 02:49:1411.02.2015 02:59:57645
      112238ABCRunning11.02.2015 02:59:5711.02.2015 03:09:25566
      112240ABCRunning11.02.2015 03:09:2511.02.2015 03:17:14471
      112258ABCRunning11.02.2015 03:17:1411.02.2015 05:28:117853
      112260ABCRunning11.02.2015 05:28:1111.02.2015 05:28:110
      112264ABCAdjusting11.02.2015 05:30:1811.02.2015 05:37:49444
      112269ABCAdjusting11.02.2015 05:37:4911.02.2015 05:59:461324
      112270ABCRunning11.02.2015 05:59:4611.02.2015 07:08:144105
      112271ABCRunning11.02.2015 07:08:1412.02.2015 01:00:0064306

       

       

      The outcome should look like this:

       

      Notification_IDMachineStatusStart_Date_TimeEnd_Date_TimeDuration_SecDate for Calender
      112233ABCRunning10.02.2015 21:29:3911.02.2015 00:00:00902110.02.2015
      112233ABCRunning11.02.2015 00:00:0011.02.2015 02:49:141015411.02.2015
      112235ABCRunning1.02.2015 02:49:1411.02.2015 02:59:5764511.02.2015
      and so on...

       

      Anyone has an idea how to code this?

       

      Greetings Lukas

        • Re: Split duration time into seperate records per date
          Manish Kachhia
          Temp:
          Load
            Notification_ID & '|' & Machine as Key,
            Notification_ID,
            Machine,
            Status,
            TimeStamp(TimeStamp#(Start_Date_Time,'DD.MM.YYYY hh:mm:ss')) as Start_Date_Time,
            TimeStamp(TimeStamp#(End_Date_Time,'DD.MM.YYYY hh:mm:ss')) as End_Date_Time,
            Duration_Sec
          Inline
          [
            Notification_ID, Machine, Status, Start_Date_Time, End_Date_Time, Duration_Sec
            112233, ABC, Running, 10.02.2015 21:29:39, 11.02.2015 02:49:14, 19175
            112235, ABC, Running, 11.02.2015 02:49:14, 11.02.2015 02:59:57, 645
            112238, ABC, Running, 11.02.2015 02:59:57, 11.02.2015 03:09:25, 566
            112240, ABC, Running, 11.02.2015 03:09:25, 11.02.2015 03:17:14, 471
            112258, ABC, Running, 11.02.2015 03:17:14, 11.02.2015 05:28:11, 7853
            112260, ABC, Running, 11.02.2015 05:28:11, 11.02.2015 05:28:11, 0
            112264, ABC, Adjusting, 11.02.2015 05:30:18, 11.02.2015 05:37:49, 444
            112269, ABC, Adjusting, 11.02.2015 05:37:49, 11.02.2015 05:59:46, 1324
            112270, ABC, Running, 11.02.2015 05:59:46, 11.02.2015 07:08:14, 4105
            112271, ABC, Running, 11.02.2015 07:08:14, 12.02.2015 01:00:00, 64306
          ];
          
          
          NoConcatenate
          Final:
          Load
            *,
            Interval(End_Date_Time-Start_Date_Time,'ss') as Duration_Sec,
            Date(Floor(Start_Date_Time)) as [Date For Calendar];
          Load
            Key
            ,Notification_ID
            ,Machine
            ,Status
            ,If(Key = Peek(Key), TimeStamp(Floor(Start_Date_Time) + IterNo() - 1), Start_Date_Time) as Start_Date_Time
            ,If(Floor(Start_Date_Time) + IterNo() < End_Date_Time, TimeStamp(Floor(Start_Date_Time) + IterNo()), End_Date_Time) as End_Date_Time
          Resident Temp
          While Floor(Start_Date_Time) + IterNo() - 1 < End_Date_Time;
          
          
          Drop Table Temp;
          Drop Field Key;