Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
PFA of Data, Here EMP_ENT_LEA Filed having values zero and One, Zero means enter and One means Leaving or Exist.
We need to find out the
1) Each employee how many hours worked per day,weekly and monthly
2) Like 8:00 AM to 9:00 AM, 9:00 Am to 10:00 AM how many employees enter to office
3) Per day how many employees worked more than 8 hours and those employee details
Regards:
Sreeni
Data:
Load
EMP_ID as EmployeeID,
EMP_ENT_LEA,
TimeStamp#(MNT_TS,'DD/MM/YYYY hh:mm') as MNT_TS,
Date(Floor(TimeStamp#(MNT_TS,'DD/MM/YYYY hh:mm'))) as Date
Inline
[
EMP_ID, EMP_ENT_LEA, MNT_TS
1407154358566, 0, 12/06/2014 09:00
1407154358566, 1, 12/06/2014 11:00
1407154358566, 0, 12/06/2014 11:16
1407154358566, 1, 05/06/2014 13:00
1407154358566, 0, 05/06/2014 14:00
1407154358566, 1, 05/06/2014 16:00
1407154358566, 0, 05/06/2014 16:30
1407154358566, 1, 05/06/2014 18:00
1407154358577, 0, 12/06/2014 10:00
1407154358577, 1, 12/06/2014 11:00
1407154358577, 0, 12/06/2014 11:16
1407154358577, 1, 05/06/2014 13:00
1407154358577, 0, 05/06/2014 14:00
1407154358577, 1, 05/06/2014 16:00
1407154358577, 0, 05/06/2014 16:30
1407154358577, 1, 05/06/2014 18:00
];
Temp:
Load
*,
IF(EmployeeID = Previous(EmployeeID) and EMP_ENT_LEA = 0 and Date = Previous(Date), Interval(MNT_TS-Previous(MNT_TS),'hh:mm:ss')) as Break
Resident Data
Order By EmployeeID, MNT_TS;
Drop Table Data;
Final:
Load
EmployeeID,
Date,
Week(Date) as Week,
Month(Date) as Month,
Year(Date) as Year,
Interval(Max(MNT_TS)-Min(MNT_TS)-SUM(Break),'hh:mm') as TotalWorkingTime
Resident Temp
Group By EmployeeID, Date;
Drop Table Temp;
Just a slight change in the data because the data wasn't making sense
[
EMP_ID, EMP_ENT_LEA, MNT_TS
1407154358566, 0, 12/06/2014 09:00
1407154358566, 1, 12/06/2014 11:00
1407154358566, 0, 12/06/2014 11:16
1407154358566, 1, 12/06/2014 13:00 //Changed from 05/06/2014 13:00
1407154358566, 0, 05/06/2014 14:00
1407154358566, 1, 05/06/2014 16:00
1407154358566, 0, 05/06/2014 16:30
1407154358566, 1, 05/06/2014 18:00
1407154358577, 0, 12/06/2014 10:00
1407154358577, 1, 12/06/2014 11:00
1407154358577, 0, 12/06/2014 11:16
1407154358577, 1, 12/06/2014 13:00 //Changed from 05/06/2014 13:00
1407154358577, 0, 05/06/2014 14:00
1407154358577, 1, 05/06/2014 16:00
1407154358577, 0, 05/06/2014 16:30
1407154358577, 1, 05/06/2014 18:00
];
I did not make sense that a employee who entered never left . So I made them exist