Help Required - Urgent

[#1]

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

[Anonymous]

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;

[#3]

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

[Anonymous]

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

[Anonymous · Accepted solution]

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

[#6]

Hi Marcus,

I am very thank full. You have solved my big issue

Thanks in advance

Regards

Venkatesh Salla