Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hey, did you manage it to work? I did an example here, you can see if this match your requirements. /Marcus
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;
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
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
Hey, did you manage it to work? I did an example here, you can see if this match your requirements. /Marcus
Hi Marcus,
I am very thank full. You have solved my big issue
Thanks in advance
Regards
Venkatesh Salla