Serviceable Hrs Logic

[#1]

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

[CELAMBARASAN · Accepted solution]

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

[#3]

Hi

Thank you very much for the prompt and correct reply

Regards

Venkatesh Salla

[#4]

HI

i would lie to know on more thing

what do if the end time is next day and i want to calculate till the current day(service able) available time

Please help on this

Thanks & Regards

Venkatesh Salla

[CELAMBARASAN]

Hi,

     For that use expression below

  if(Num(Avail_EndTime) <= Num(MakeDate(Year(Avail_StartTime),Month(Avail_StartTime),Day(Avail_StartTime)) + Time(AccessEndTime)), Avail_EndTime, Date(MakeDate(Year(Avail_StartTime),Month(Avail_StartTime),Day(Avail_StartTime))+Time(AccessEndTime),'DD/MM/YYYY hh:mm:ss TT')) AS NewEndTime

Hope this is your need.

Celambarasan