Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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;
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;
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!!!