2

I would like to sum the hours for each name, giving a total interval between START and END activities. It would be simple if I could subtract from each record the end of the beginning. For example, Mary started at 13:00 and worked up to 15:00 and started another activity from 14:00 to 16:00. I would like the result to be 3 (she used 3 hours of their time to perform both activities).

Example data

Name    |    START               |    END                 |
-----------------------------------------------------------
KATE    | 2014-01-01 13:00:00    | 2014-01-01 14:00:00    |
MARY    | 2014-01-01 13:00:00    | 2014-01-01 15:00:00    |
TOM     | 2014-01-01 13:00:00    | 2014-01-01 16:00:00    |
KATE    | 2014-01-01 12:00:00    | 2014-01-02 04:00:00    |
MARY    | 2014-01-01 14:00:00    | 2014-01-01 16:00:00    |
TOM     | 2014-01-01 12:00:00    | 2014-01-01 18:00:00    |
TOM     | 2014-01-01 22:00:00    | 2014-01-02 02:00:00    |

Result

KATE    15 hours
MARY     3 hours
TOM      9 hours
1

You could merge overlapping intervals and count the hours from there (untested):

select name, min(start), end 
from (
    select x.name, x.start, min(y.end) as end 
    from t as x 
    join t as y 
        on x.name = y.name 
       and x.start <= y.end 
       and not exists (
           select 1 
           from t as z 
           where y.name = z.name 
             and y.end >= z.start 
             and y.end < z.end
       ) 
    where not exists (
        select 1 
        from t as u 
        where x.name = u.name 
          and x.start > u.start 
          and x.start <= u.start
    ) 
    group by x.name, x.start
) as v group by name, end;
| improve this answer | |
0

Consider generating a table of hours as timestamps:

HOUR
----
2014-10-22 12:00
2014-10-22 13:00
2014-10-22 14:00
2014-10-22 15:00
...

Then you can select the hours that are between the periods of your original table.

Something like the following:

select name, count(distinct hour) 
from HOUR join PERIOD 
where hour between PERIOD.start and PERIOD.end
group by name
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.