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