Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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