Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi
Thank you very much for the prompt and correct reply
Regards
Venkatesh Salla
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
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