Indicators for Time Sheet

[DoraS]

Hi, 

We have a table with all times that our employees get in and get out from work and we need to calculate work time.

It's something like this:

Maria | Get In | 09:00 AM

Maria | Get out | 11:00 AM

Maria | Get In | 01:00 PM

Maria | Get Out | 05:00 PM

How can we transform the data so that we are able to count that Maria works from 09:00 AM to 11:00AM and from 01:00PM to 05:00 PM?

Thanks in advance

Dora

[stevejoyce]

I would have each row as a "shift" (start/end) for employee.  column for get-in, column for get-out, and you can find duration between them as duration.

data:
load *
,if(rowno()=1, 1, if(peek(Name) = Name and Event = 'Get Out', peek(ShiftId), peek(ShiftId)+1)) as ShiftId
;
load * inline [
Name | Event | Time
Maria | Get In | 09:00 AM
Maria | Get Out | 11:00 AM
Maria | Get In | 01:00 PM
Maria | Get Out | 05:00 PM
John | Get In | 01:00 PM
John | Get Out | 05:00 PM
]
(delimiter is '|')
;

data_final1:
load distinct
ShiftId,
Name
resident data
;

left join (data_final1)
load distinct
ShiftId,
time#(Time,'hh:mm TT') as Shift_Start
resident data
where lower(Event) = 'get in'
;

left join (data_final1)
load distinct
ShiftId,
time#(Time,'hh:mm TT') as Shift_End
resident data
where lower(Event) = 'get out'
;

data_final2:
load
*
,interval(Shift_End - Shift_Start, 'h') as [ShiftDuration (hours)]
resident data_final1;

drop tables data, data_final1;

exit script;