1 Reply Latest reply: Jul 3, 2013 1:44 AM by Kaushik Solanki RSS

    sum hours per day

    Yaniv Egozi

      G'day,

       

      I have a little issue, and have exhausted my ideas and found no solution...

      I am trying to calculate the time a person worked per day and calculate his overtime (more then 8 hours a day).

      My data is

      ID, store, date,SALE_WORKING_HOURS

      5,99,01/06/13,04:37

      5,66,01/06/13,05:00

      5,99,02/06/13,04:00

       

      In this example the person (id 5) worked in 2 diffrent stores a total of 13 hours and 37 minutes

      when I calculate his total hours SALE_WORKING_HOURS I am getting 14:37

      but when I am calculating his over time it calculate it per SALE_WORKING_HOURS per store and it shows that he has no over time when on the 01/06/13 there should be 01:37 hours of overtime.

       

      to calculate the total hours I am using

      sum(SALE_WORKING_HOURS)

       

      to calculate his full time (his up to 08:00 hours perday I am using (which he get as 100% of his salary))

      sum(if(Weekday <> 'SAT' and  SALE_WORKING_HOURS < '08:00' and HOLYDAY_TRUE<>1,SALE_WORKING_HOURS,if(Weekday <> 'SAT' and SALE_WORKING_HOURS >= '08:00'  and HOLYDAY_TRUE<>1, '08:00')))

       

      to calculate his overtime (the hours he worked more then 08:00 hours a day and less then 10:00 hoursI am using (which he get as 125% of his salary)

      sum

      (if(Weekday <> 'SAT'  and HOLYDAY_TRUE<>1,if(SALE_WORKING_HOURS <= '08:00', '00:00', if(SALE_WORKING_HOURS > '08:00' and SALE_WORKING_HOURS <'10:00', SALE_WORKING_HOURS - '08:00', if(SALE_WORKING_HOURS > '10:00','02:00','00:00')))))

       

       

      Any thoughts?

      Thank you all!!!











        • Re: sum hours per day
          Kaushik Solanki

          Hi,

           

               Try this.

           

           

          DATA:
          Load ID,store,date(date#(date,'DD/MM/YY')) as DATE,WeekDay(date(date#(date,'DD/MM/YY'))) as WeekDay,
          num(left(SALE_WORKING_HOURS,2)) as Working_Hr,num(right(SALE_WORKING_HOURS,2)) as Working_Min,SALE_WORKING_HOURS,
          ID&'-'&date(date#(date,'DD/MM/YY')) as KEY
          inline [
          ID, store, date,SALE_WORKING_HOURS
          5,99,01/06/13,04:37
          5,66,01/06/13,05:00
          5,99,02/06/13,04:00
          5,69,02/06/13,03:00
          5,69,03/06/13,02:00
          5,99,03/06/13,07:40
          5,99,04/06/13,07:00
          5,69,04/06/13,01:00
          ];


          Overtime:
          Load ID&'-'&DATE as KEY,if(sum(Working_Hr+Working_Min)>8,'Overtime','Regulartime') as Flag,
          if(sum(Working_Hr+Working_Min)>8,sum(Working_Hr+(Working_Min/100))-8,sum(Working_Hr+(Working_Min/100)))as Working_TIME
          Resident DATA
          Group by ID,DATE;

           

           

          Regards,

          Kaushik Solanki