Calculating total hours logged (with overlapping minutes)

[Anonymous]

Hi all,

I am wanting to know if there is a way to calculate total hours logged by each worker in a given day? I have a total hours column, however, an employee can log time for different tasks that overlap.

E.g. task A time logged: 11.00am-11.30am

       task B time logged: 11.10am-11.45am

If i just calculate total hours as the sum, this will give me 75 mins (30+45), however the total time logged should actually be 45 mins (since they started at 11 and finished at 11.45).

Any help greatly appreciated

Thank you,

[richardburling]

Hi, 

I have been looking for a solution to this but have not yet foudn the answer. Has anyone know of a post or a link to a website that can give me the answer?

Thanks

RB

[Anonymous]

@Anonymous 

If you have a task where you need to calculate total time but without counting overlapping periods multiple times, then the best way is to expand the periods into the required granularity (hours, minutes or seconds), and then just do DISTINCTCOUNT on their UNION. This is by far the best and THE FASTEST way to calculate such things because it only requires the Storage Engine to go to work. No Formula Engine needed.

[amitchandak]

@Anonymous , can you share some raw data in the exact format, This needs overlapping period calculation based on employee and date and create min max time to take diff.

One idea is that if the task starts in between the previous task we can end the time the previous one.