Total duration of events, possible gaps and interval overlaps
There is a list of time slots. There is always a start and an end datetime, for example:
TimeFrom TimeTill
2014-07-10 07:00:00.000 2014-07-10 11:30:00.000
2014-07-10 13:00:00.000 2014-07-10 14:00:00.000
2014-07-10 13:00:00.000 2014-07-10 14:30:00.000
Both TimeFrom
and are TimeTill
always on the same day, no exceptions. Let's say this means time spent on various projects (three in this case). Overlaps allowed.
I would like to ask for help with a SQL Server 2008 R2 query to get the total length of stay of a person for a day.
Please note that there is no opening period. In this case, it is between 11:30 and 13:00.
When I use min(TimeFrom)
and max(TimeTill)
to calculate the duration between earliest start and last end, it works great for overlaps, except that I cannot subtract the no-work interval.
The desired output is something along this line:
Day TimeWorking
2014-07-10 360
Your help is greatly appreciated!
Floor
source to share
You can create a list of 1 minute intervals for the whole day. The query exists
can filter out minutes without work. The total counter is the number of minutes in which the work was completed:
; with all_minutes as
(
select cast('00:00' as time) as time
union all
select dateadd(minute, 1, time)
from all_minutes
where time < cast('23:59' as time)
)
select cast(yt.TimeFrom as date) as day
, count(distinct am.time) as minutes_worked
from YourTable yt
left join
all_minutes am
on cast(yt.TimeFrom as time) <= am.time
and am.time < cast(yt.TimeTo as time)
group by
cast(TimeFrom as date)
option (maxrecursion 0)
;
source to share