Working Hours

[#1]

Hi,

I have the below data:

empno	name	type	no	fac	time	date	status
123	abc	12	99	6	6:49:24	8/7/2012	Time IN
123	abc	12	99	6	8:51:53	8/7/2012	Time OUT
123	abc	12	99	6	10:30:47	8/7/2012	Time IN
123	abc	12	99	6	15:16:44	8/7/2012	Time OUT
123	abc	12	99	6	23:30:47	8/9/2012	Time IN
123	abc	12	99	6	7:16:44	8/10/2012	Time OUT

On 7th, it is easy for me to calculate the Working Hours with Max(OutTime) - Min (InTime).

But for 9th and 10th, how to calculate the Working Hours.?

It will be 10/8/2012 7:16 AM - 09/08/2012 11:30 PM but I am unable to get the values.

Please help...

[#2]

Use the Interval() function.

[SunilChauhan]

t2:

INLINE [

    empno, name, type, no, fac, time, date, status

    123, abc, 12, 99, 6, 6:49:24, 08-07-2012, Time IN

    123, abc, 12, 99, 6, 8:51:53, 08-07-2012, Time OUT

    123, abc, 12, 99, 6, 10:30:47, 08-07-2012, Time IN

    123, abc, 12, 99, 6, 15:16:44, 08-07-2012, Time OUT

    123, abc, 12, 99, 6, 23:30:47, 08-09-2012, Time IN

    123, abc, 12, 99, 6, 7:16:44, 08-10-2012, Time OUT

];

T1:

Load *,

if(previous(status)='Time IN' and status='Time OUT' and previous(empno)=empno,interval(date+time -previous(date+time)),'') as nethour

resident t2;

drop table t2;

Please try this hope this helps

[#4]

Hi,

Thank you but i dont knw where I am going wrong. It is not working.. It is still taking the last out time on that day and In time on that day, it is not taking the next day value. It is also otherwise calculating Working Hours between every In and Out Status.

Eg: 10-8-12.. (Normal Timings)

1. I need to pick the Min In Time -- First In Time.

2. I need to pick the Max In Time - Last Out Time.

3. Working Hrs = Max Hrs - Min Hrs

Eg: 11-8-12 (Night Shift)

1. I need to pick the Min In Time (11-8-12 First In Time).

2. I need to pick the Max In Time (12-8-12 Last Out Time).

3. Working Hrs = Max Hrs - Min Hrs (12-8-12 Last Out Time - 11-8-12 First In Time)

I have another problem, here in case my first in time is 22:00 and have even 00:24 time in my data, then min(time) is taken as 00:24. So calculation goes wrong...

[#5]

Hi,

Any solution..?

Thanks...

[jagan]

Hi,

Please find attached file, hope this is what you are expecting.

Regards,

Jagan.

[#7]

Hi Jagan,

For 9 and 10th Calculation is perfect..

bt on 7th, it has to ideally calculate, 15:16 (last out time) - 6:49 (first in time)...  which is 8:27.. But it calculates in between every in time too... and I am sure the same might happen w.r.t night shift too if there are many in and out time.

Thanks...

[jagan]

Hi Shilpa,

PFA.

Regards,

Jagan.

[#9]

Hi Jagan,

That works fine and after reloading I realised i've missed a possibility... would this code hold good even for the below data

empno	name	type	no	fac	time	date	status	Shift (only for reference, not part of the data)
123	abc	12	99	6	6:49:24	8/7/2012	Time IN	First
123	abc	12	99	6	8:51:53	8/7/2012	Time OUT
123	abc	12	99	6	10:30:47	8/7/2012	Time IN
123	abc	12	99	6	15:16:44	8/7/2012	Time OUT
123	abc	12	99	6	23:30:47	8/9/2012	Time IN	Third
123	abc	12	99	6	7:16:44	8/10/2012	Time OUT
123	abc	12	99	6	23:15:00	8/10/2012	Time IN	Third
123	abc	12	99	6	0:03:00	8/11/2012	Time OUT
123	abc	12	99	6	2:50:00	8/11/2012	Time IN
123	abc	12	99	6	6:45:00	8/11/2012	Time OUT
123	abc	12	99	6	15:45:00	8/11/2012	Time IN	Second
123	abc	12	99	6	17:00:00	8/11/2012	Time OUT
123	abc	12	99	6	17:02:00	8/11/2012	Time IN
123	abc	12	99	6	21:00:09	8/11/2012	Time OUT
123	abc	12	99	6	22:23:00	8/11/2012	Time IN
123	abc	12	99	6	23:00:00	8/11/2012	Time OUT

Thanks...

[jagan]

Hi Shilpa,

I think you are referring below values, I think the code which I given doesn't handle this

8/10/2012

Time IN

8/11/2012

Time OUT

There should be some field which holds the working date or some thing which tells you on which working day the entry belongs to. Then only it is possible, with this data it is difficult to handle.

Regards,

Jagan.