Time Spent in/outside Room

[dmohanty]

Hi All,

Need a quick help in calculating the time spent by a person (for a month) outside the room at each Swipe OUT, Total Time spent outside room (summation of each time outside), and the Total Time Spent in Office (First Swipe IN time - Last Swipe OUT).

Here is my sample data:

Room	Card No	Swipe Date	SwipeTime	Swipe Status
Room A	12345	2013-04-01	12:32:57	Time IN
Room A	12345	2013-04-01	13:34:50	Time OUT
Room A	12345	2013-04-01	14:59:13	Time IN
Room A	12345	2013-04-01	14:59:48	Time OUT
Room A	12345	2013-04-01	15:00:44	Time IN
Room A	12345	2013-04-01	16:33:21	Time OUT
Room A	12345	2013-04-01	18:06:09	Time IN
Room A	12345	2013-04-01	18:11:24	Time OUT
Room A	12345	2013-04-02	12:28:04	Time IN
Room A	12345	2013-04-02	14:07:08	Time OUT
Room A	12345	2013-04-02	15:40:06	Time IN
Room A	12345	2013-04-02	16:30:19	Time OUT

So I need an extra column "Time Outside Room" to be calculated as  if Swipe Status = 'Time IN' , SwipeTime - Previous(SwipeTime). So, the new records would look like this:

Room	Card No	Swipe Date	SwipeTime	Swipe Status	Time Outside Room	Total Hours
Room A	12345	2013-04-01	12:32:57	Time IN
Room A	12345	2013-04-01	13:34:50	Time OUT
Room A	12345	2013-04-01	14:59:13	Time IN	1:24	04:28:00
Room A	12345	2013-04-01	14:59:48	Time OUT
Room A	12345	2013-04-01	15:00:44	Time IN	0:0
Room A	12345	2013-04-01	16:33:21	Time OUT
Room A	12345	2013-04-01	18:06:09	Time IN	1:32
Room A	12345	2013-04-01	18:11:24	Time OUT
Room A	12345	2013-04-02	12:28:04	Time IN
Room A	12345	2013-04-02	14:07:08	Time OUT
Room A	12345	2013-04-02	15:40:06	Time IN	1:32
Room A	12345	2013-04-02	16:30:19	Time OUT

Please help in calculating the values of the Last two fields (Time Outside Room, Total Hours) in above Table.

Appreciate your help!!!

[swuehl · Accepted solution]

You probably need to define the granularity you want to look at when calculating the TOTAL values.

I assumed something like the combination of Room, card number and Swipe Date.

You could then try something like

INPUT:

LOAD *, AutoNumber([Card No]&Room&[Swipe Date]) as Gran

INLINE [

Room,           Card No,          Swipe Date,          SwipeTime,          Swipe Status

Room A,          12345,          2013-04-01,          12:32:57,          Time IN

Room A,          12345,          2013-04-01,          13:34:50,          Time OUT

Room A,          12345,          2013-04-01,          14:59:13,          Time IN

Room A,          12345,          2013-04-01,          14:59:48,          Time OUT

Room A,          12345,          2013-04-01,          15:00:44,          Time IN

Room A,          12345,          2013-04-01,          16:33:21,          Time OUT

Room A,          12345,          2013-04-01,          18:06:09,          Time IN

Room A,          12345,          2013-04-01,          18:11:24,          Time OUT

Room A,          12345,          2013-04-02,          12:28:04,          Time IN

Room A,          12345,          2013-04-02,          14:07:08,          Time OUT

Room A,          12345,          2013-04-02,          15:40:06,          Time IN

Room A,          12345,          2013-04-02,          16:30:19,          Time OUT

];

RESULT1:

LOAD *,

           if(Gran = Peek(Gran) and [Swipe Status] = 'Time IN',

                               interval(SwipeTime-peek(SwipeTime))) as TimeOut

Resident INPUT

order by [Card No], Room, [Swipe Date], SwipeTime asc;

DROP TABLE INPUT;

RESULT2:

LOAD Gran,

           interval(sum(TimeOut)) as TotalTimeOut,

           interval(

                     max(if([Swipe Status] = 'Time OUT', SwipeTime))

            - min(if([Swipe Status] = 'Time IN', SwipeTime))

                                ) as TotalTimeInOffice

Resident RESULT1 group by Gran;

[dmohanty]

Thanks a lot Swuehl for the quick turnaround.

Yes, you are right that a Key needed to be created. I should have told you to note the Swipe Date as important. Thanks anyways for figuring it out!!!

I may seek some more help later from you to calculate the Efforts of the people coming in Shifts. Ex: If a person Swipes IN at 10:00 pm (Monday) and Swipes OUT at 6:00 am next day (Tuesday) or else with some more conditions. I will post sample data later.

Thanks and have a great day!!!