5 Replies Latest reply: Apr 6, 2012 10:56 AM by venkateshtechnoforte RSS

    Help Required - Urgent

      Dear All

       

       

      How do i get the extact outofservice hours from the below example. Please find  the attached excel to understand he requirement.

       

      For example, if u look @

       

      INC000000076899

       

      The down time is calculated as 69.81666667 (Hrs). but if u look @ the  matrix avaliblity it is from morning 7:30 to evening 5:30 pm

       

      so i have to calulate only the duration from between the access start and end time's.

       

      according to this logic if we calculate the down time duartion should be

       

      since it down from 5-02-2012 13:05:00 to 08/02/2012 10:54:18

       

      the as per logic

       

      on 5th feb it shoul be from 05-02-2012 13:05:00 to 05-02-2012 17:30:00 i.e 5 Hrs 25 Mns

      on 6th feb it shoul be from 06-02-2012 07:30:00 to 06-02-2012 17:30:00 i.e 10 Hrs

      on 7th feb it shoul be from 07-02-2012 07:30:00 to 07-02-2012 17:30:00 i.e 10 Hrs

      on 8th feb it shoul be from 08-02-2012 07:30:00 to 08-02-2012 10:54:18 i.e 3 Hrs 20 mns

       

      so the total down time duration should be around 30 Hrs only

       

      How to achive this

       

      Thanks in Advance

       

      Regards

      Venkatesh Salla

        • Re: Help Required - Urgent
          Marcus Persson

          I think you can break it down something like this:

           

          Load
          if(TotalHours < 10, TotalHours, ((TotalHours - HoursFirstDay - HoursLastDay) /24) * 10 + CalculatedHoursFirstDay + CalculatedHoursLastDay) as Downtime,
          *;
          Load
          Interval(End_Time - Start_Time) TotalTime,
          17:30 - Start_Time as CalculatedHoursFirstDay,
          23:59 - Start_Time as HoursFirstDay,
          End_Time - 07:30 as CalculatedHoursLastDays,
          End_Time - 00:00 as HoursLastDay,
          *;
          LOAD
          ..

          drop field TotalTime;
          drop field CalculatedHoursFirstDay;
          drop field HoursFirstDay;
          drop field CalculatedHoursLastDays;
          drop field HoursLastDay;

            • Re: Help Required - Urgent

              HI Marcus,

               

              I am sorry but can please take some time explain in detail, with respect

              to my data and fileds name Please

               

               

              Thanks in advance

               

              Regards

              Venkatesh Salla

                • Re: Help Required - Urgent
                  Marcus Persson

                  The first Load is just a load of your Excel sheet, the the other two preceding loads are for calculating the downtime.
                  - So if the duraion is shorter than 10 hours, the duration is correct,
                  - else we take the duration of all the whole days divide with 24 and * 10 (or AccessEndTime - AccessStartTime) in order to only get the hours from the whole days.
                  - then we add the calculated down time from the first and last day.


                  Load
                  if(Duration,< 10, Duration,((Duration - HoursFirstDay - HoursLastDay) /24) * 10 + CalculatedHoursFirstDay + CalculatedHoursLastDay) as Downtime,
                  *;

                  Load
                  AccessEndTime- Start_Time as CalculatedTimeFirstDay,
                  23:59 - Start_Time as TimeFirstDay,
                  End_Time - AccessStartTime as CalculatedTimeLastDays,
                  End_Time - 00:00 as TimeLastDay,
                  *;

                  LOAD
                  ID,
                  Start_Time,
                  End_Time,
                  Duration,
                  AccessStartTime,
                  AccessEndTime,
                  ..
                  From CH148....xls;

                  //these field can be dropped since they are only used to calculate the downtime
                  drop field TotalTime;
                  drop field CalculatedHoursFirstDay;
                  drop field HoursFirstDay;
                  drop field CalculatedHoursLastDays;
                  drop field HoursLastDay;

                  //of course you will need to chech syntax and formats in order to make this work

                  • Re: Help Required - Urgent
                    Marcus Persson

                    Hey, did you manage it to work? I did an example here, you can see if this match your requirements. /Marcus