duration over multiple days

[michaeldburt]

Hello Everyone-

I am trying to figure out the easiest way to split a field that tracks duration into two rows.  For example:

I begin ProcessA at 23:00 on 6/22/15, and it completes at 02:00 on 6/23/15.  The duration is 3 hours.  I want to create two rows, the first with a duration of 1 hour, with a start date of 6/22/15 23:00 and the second with a duration of 2 hours with a start date of 6/23/15 00:00.

Currently, I have 1 row, with a Start Date of 6/22 and a Complete Date of 6/23 and a duration of 3 hours.

Any suggestions?

[swuehl]

I think you can start with something like this:

Set TimestampFormat = 'M/D/YY hh:mm TT';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM,    7/6/12 4:38 PM

6/29/12 4:45 PM,    7/6/12 4:19 PM  

8/1/12 09:00 AM, 8/3/12 10:00 AM

8/3/12 03:00 PM, 8/6/12 09:00 AM

];

RESULT:

LOAD *, interval(End-Start,'hh:mm:ss.fff') as Duration;

LOAD  ID,

Date(daystart(DT1)+iterno()-1) as Date,

Time(if(iterno()=1, frac(DT1), 0)) as Start,

Time(if(daystart(DT1)+iterno()-1=daystart(DT2),frac(DT2), dayend(0) )) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

[sunny_talwar]

Something like this:

Script:

Table:

LOAD Process,

  Timestamp#(StartTimeStamp, 'M/DD/YY hh:mm') as StartTimeStamp,

  Timestamp#(EndTimeStamp, 'M/DD/YY hh:mm') as EndTimeStamp;

LOAD * Inline [

Process, StartTimeStamp, EndTimeStamp

ProcessA, 6/22/15 23:00, 6/23/15 02:00

];

FinalTable:

LOAD Process,

  StartTimeStamp,

  EndTimeStamp,

  Interval(DayEnd(StartTimeStamp) - StartTimeStamp + (1/(24*60*60)), 'hh:mm') as Hours

Resident Table;

Concatenate (FinalTable)

LOAD Process,

  StartTimeStamp,

  EndTimeStamp,

  Interval(EndTimeStamp - DayStart(EndTimeStamp), 'hh:mm') as Hours

Resident Table;

DROP Table Table;