Calculate total seconds of time intervals

问题:

Considering a table that contain informations with time intervals like this:

ID  StartTime             EndTime
==================================================
1   2012-11-22 06:14:10   2012-11-22 06:18:00
2   2012-11-22 06:16:10   2012-11-22 06:19:40
3   2012-11-22 06:20:50   2012-11-22 06:21:20
4   2012-11-22 06:22:30   2012-11-22 06:23:00
5   2012-11-22 06:22:10   2012-11-22 06:24:40
..................................................
..................................................

The problem is find the better t-sql way for calculate total seconds of these intervals considering intersections time only one time.

For example, total seconds of the first three records is 360.

回答1:

Here is an example, the script is finding the intervals where the seconds were not included in any rows and subtract it from the sum of the seconds between first 'start' and last 'end'. The script is assuming that the starttime is always smaller or equal to the endtime

select max(totalsec) - coalesce(sum(datediff(second, t.endtime, a.starttime)),0) from <table> t
cross apply
(select min(starttime) starttime from <table> where t.endtime < starttime) a
cross apply(select datediff(second, min(starttime), max(endtime)) totalsec from <table>) b
where 
not exists (select 1 from <table> where id <> t.id and t.endtime >= starttime and t.endtime < endtime)