2 Replies Latest reply: Sep 12, 2015 5:18 AM by Sunny Talwar

# Need to find out how many hours working each employee

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

• ###### Re: Need to find out how many hours working each employee

Data:

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:

*,

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:

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;

• ###### Re: Need to find out how many hours working each employee

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