Need to find out how many hours working each employee

[sreeni_qvd]

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

[MK_QSL]

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;

[sunny_talwar]

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