Calculation of Out of Service Duration

[#1]

Dear all,

Please help us in deriving thislogic. We would be thank full

Let me explain the scenario,

This is for ATM Down Time calc.

One ATM may be out ofservice(down time) for many reasons

ATM(Terminal ddd) data as a sample is attached for your ref;

In data u will find that thefirst fault started let say for example

TermId	FaultStartTime	FaultEndTime
ddd	11/13/11  0:00	11/13/11  5:39
ddd	11/13/11  5:31	11/13/11  5:39
ddd	11/13/11  8:37	11/13/11  10:19
ddd	11/13/11  10:06	11/13/11  10:19
ddd	11/13/11  19:32	11/14/11  0:00

Fault1 started @ 12 AM(0:00) andfixed @ 5:39 AM

Fault2  started @ 5:31 amand fixed @ 5:39 AM (here what is required is any number fault’s which has astart time which is less the fault 1 end time will not be considered) the reason is assuming  that  the subseqwent faults occured because of fault1.

If fault2 end time is greaterthan the fault 1 end time then we have to calculate  as

(fault2 end time  - fault 1start time) as out of service time.

Now if fault 3 has anyoverlapping with fault(1,2) end time then same as above

Or

if fault 3 has any nooverlapping with the earlier faults the it should be consider as new fault

I hope I made myself clear onthis, if any further clarification id write to me.

[swuehl · Accepted solution]

I think you just need to go through your list of faults twice, one time with ascending Timestamp, one time with descending Timestamp (In attached sample, I used RecID for order, but now I think it might be better to use the FaultStartTime.).

Then use peek() function and some comparisons to apply your logic. Like this:

INPUT:

LOAD Recno() as RecID,

     TermId,

     FaultStartTime,

     FaultEndTime

FROM

TestData.xls

(biff, embedded labels, table is Sheet1$);

INTERMED:

LOAD *,

if(TermId=Peek(TermId), if(FaultStartTime>peek(FaultEndTime), FaultStartTime),FaultStartTime) as StartTime

resident INPUT order by RecID asc;

drop table INPUT;

INTERMED2:

LOAD *,

if(TermId=Peek(TermId), if(FaultEndTime<peek(FaultEndTime) and peek(FaultStartTime)<FaultEndTime, peek(FaultEndTime),FaultEndTime),FaultEndTime) as EndTime 

resident INTERMED order by RecID desc;

drop table INTERMED;

RESULT:

LOAD Rowno() as FaultID, TermId, StartTime as FaultStartTime, EndTime as FaultEndTime resident INTERMED2 where not isnull(StartTime);

drop table INTERMED2;

[#3]

Hi swuehl

Thank you for reply and logic, It  is same as what i was expecting .

I would be troubling you for few more things like this I

Thanks & Regards

Venkatesh Salla