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

    Need to find out how many hours working each employee

    sreenu k

      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
          Manish Kachhia

          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;

            • Re: Need to find out how many hours working each employee
              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