I am building a data model of employee shifts, so at any hour the application will reveal what department they are in. I have a single column of clock in times, and another column that gives the legend for the type of clock in, in, out, and startshift.
This is what I've done so far, I broke out the times to determine which is a start time and end time. The idea was to use interval match to find out how many were on staff at any given hour. I build out the calendar and the date hour field does not work. When I broke out my start shift and stop shift from the singe column, when I loaded the data into a table box, either the start time or stop time is null. So if an employee clock in once, it will show like this:
The employee shift times is in one column like this.
1/01/20116 8:00 am
01/01/2016 5:00 pm
Thank you Kindly for your help.
start time end time
9 am
5pm.
Here is the script:
Shift:
Load Distinct
*,
IF(Match(Shift_Flg, 'ClockIn'),date(EmployeePunchDateTime,'MM/DD/YYYY hh:mm' )) AS [ShiftStart],
IF(Match(Shift_Flg, 'ClockOut'),date(EmployeePunchDateTime, 'MM/DD/YYYY hh:mm' )) AS [ShiftEnd]
;
Load
EmployeeNumber,
PUNCHDTM as EmployeePunchDateTime,
date(floor(PUNCHDTM)) AS EmployeePunchDate,
timestamp(floor(PUNCHDTM) + Maketime(hour(PUNCHDTM)), 'MM/DD/YYYY hh') as ShiftHour,
Time(PUNCHDTM) as EmployeePunchTime,
if(PUNCHOVERRIDE='New Shift' or PUNCHOVERRIDE='NULL' or PUNCHOVERRIDE='In Punch', dual('ClockIn',1),dual('ClockOut',0)) as Shift_Flg,
PUNCHOVERRIDE as EmployeePunchStatus
Resident Employee;
date_temp:
LOAD
min(floor(ShiftStart)) as mindate,
max(floor(ShiftEnd)) as maxdate
RESIDENT Shift;
;
LET vMindate = peek('mindate')-1;
LET vMaxdate = peek('maxdate');
DROP TABLE date_temp;
FOR i = 0 to 23 // Hour 0 to 23
Calendar:
LOAD *,
timestamp(Date + Maketime($(i)), 'MM/DD/YYYY hh') as DateHour,
$(i) as Hour
;
LOAD
date($(vMindate) + IterNo()) as Date
AUTOGENERATE 1
WHILE $(vMindate) + IterNo() <= $(vMaxdate)
;
NEXT i
left join(Shift)
IntervalMatch (DateHour)
LOAD ShiftStart, ShiftEnd
RESIDENT Shift
;
