Split duration time into seperate records per date

[luke1986]

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_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

The outcome should look like this:

Notification_ID	Machine	Status	Start_Date_Time	End_Date_Time	Duration_Sec	Date for Calender
112233	ABC	Running	10.02.2015 21:29:39	11.02.2015 00:00:00	9021	10.02.2015
112233	ABC	Running	11.02.2015 00:00:00	11.02.2015 02:49:14	10154	11.02.2015
112235	ABC	Running	1.02.2015 02:49:14	11.02.2015 02:59:57	645	11.02.2015
and so on...

Anyone has an idea how to code this?

Greetings Lukas

[MK_QSL · Accepted solution]

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;

[luke1986]

Thank you Manish, that worked!