2 Replies Latest reply: Feb 2, 2012 12:06 PM by venkateshtechnoforte RSS

    Calculation of Out of Service Duration

      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











      11/13/11  0:00


      11/13/11  5:39



      11/13/11  5:31


      11/13/11  5:39




      11/13/11  8:37


      11/13/11  10:19




      11/13/11  10:06


      11/13/11  10:19




      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




      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.



        • Re: Calculation of Out of Service Duration
          Stefan Wühl

          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:



          LOAD Recno() as RecID,






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



          LOAD *,

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

          resident INPUT order by RecID asc;

          drop table INPUT;



          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;



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


          drop table INTERMED2;