sum hours per day

[egoziyan]

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!!!

[kaushiknsolanki]

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