4 Replies Latest reply: Feb 14, 2012 1:37 AM by Celambarasan Adhimulam RSS

    Serviceable Hrs Logic

      Hi

       

      Please find the attached excel sheet for understanding the requirement.

       

      The data is the raw data on which we need to put the logic below:

       

      Based on

      Avail Starttime and Avail EndTime we have to calculate the new start time and end time as enterted in the excel.

       

      this has to be calculated based the sheet2 (service hrs)

       

      The rquirement is we have to caluculation the down time based on the servicable Hrs only

       

      For example

       

      IF TermID 1 is availble from 6:00 Am to 4:00 PM

       

      and if a fault had happened

       

      5:00 AM and Fixed @ 7:00 Am the we have to calculate on from 6:00 amd to 7:00 and i.e. 1 hrs instead of 2 hrs.

       

      Becuase it is available from 6 'o clock only

       

      Please help me on the same.

       

       

      Thanks and Regards

      Venkatesh Salla

        • Re: Serviceable Hrs Logic
          Celambarasan Adhimulam

          Hi,

               Try with this approach,

           

           

          TempTable:
          LOAD TermId,
               [Customer Access],
               Avail_StartTime,
               Avail_EndTime
          FROM
          DataFile;
          
          Left Join(TempTable)
          LOAD [Customer Access],
               AccessStartTime,
               AccessEndTime
          FROM
          DataFile;
          
          
          MainTable:
          LOAD
                    TermId,
                    [Customer Access],
                    Avail_StartTime,
            Avail_EndTime,
            if(Num(Avail_StartTime) >= Num(MakeDate(Year(Avail_StartTime),Month(Avail_StartTime),Day(Avail_StartTime)) + AccessStartTime), Avail_StartTime, Date(MakeDate(Year(Avail_StartTime),Month(Avail_StartTime),Day(Avail_StartTime))+AccessStartTime,'DD/MM/YYYY hh:mm:ss TT')) AS NewStartTime,
                    if(Num(Avail_EndTime) <= Num(MakeDate(Year(Avail_EndTime),Month(Avail_EndTime),Day(Avail_EndTime)) + Time(AccessEndTime)), Avail_EndTime, Date(MakeDate(Year(Avail_EndTime),Month(Avail_EndTime),Day(Avail_EndTime))+Time(AccessEndTime),'DD/MM/YYYY hh:mm:ss TT')) AS NewEndTime
          Resident
          TempTable;
          
          
          DROP Table TempTable;
          
          

           

          Hope it helps

           

          Celambarasan