5 Replies Latest reply: Apr 6, 2012 10:56 AM by venkateshtechnoforte

# Help Required - Urgent

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

Regards

Venkatesh Salla

• ###### Re: Help Required - Urgent

I think you can break it down something like this:

if(TotalHours < 10, TotalHours, ((TotalHours - HoursFirstDay - HoursLastDay) /24) * 10 + CalculatedHoursFirstDay + CalculatedHoursLastDay) as Downtime,
*;
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,
*;
..

drop field TotalTime;
drop field CalculatedHoursFirstDay;
drop field HoursFirstDay;
drop field CalculatedHoursLastDays;
drop field HoursLastDay;

• ###### Re: Help Required - Urgent

HI Marcus,

I am sorry but can please take some time explain in detail, with respect

to my data and fileds name Please

Regards

Venkatesh Salla

• ###### Re: Help Required - Urgent

- 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.

if(Duration,< 10, Duration,((Duration - HoursFirstDay - HoursLastDay) /24) * 10 + CalculatedHoursFirstDay + CalculatedHoursLastDay) as Downtime,
*;

AccessEndTime- Start_Time as CalculatedTimeFirstDay,
23:59 - Start_Time as TimeFirstDay,
End_Time - AccessStartTime as CalculatedTimeLastDays,
End_Time - 00:00 as TimeLastDay,
*;

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

• ###### Re: Help Required - Urgent

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

• ###### Re: Help Required - Urgent

Hi Marcus,

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