CODEWITHSUNDEEP
sqlsql-serversql-server-2012
controller
controller

Reputation: 195

Find Overlap In Time Records

I have a table in a SQL Server 2012 database that logs events, with columns StartDate and EndDate. I need to aggregate all of the records within a certain time period, and determine the duration of time where any events were active, not counting overlapping durations. For example, if my table looks like this:

 id     StartDate                EndDate
 =======================================================
 1     2017-08-28 12:00:00 PM    2017-08-28 12:01:00 PM
 2     2017-08-28 1:15:00 PM     2017-08-28 1:17:00 PM
 3     2017-08-28 1:16:00 PM     2017-08-28 1:20:00 PM
 4     2017-08-28 1:30:00 PM     2017-08-28 1:35:00 PM

And my time period to search was from 2017-08-28 12:00:00 PM to 2017-08-28 2:00:00 PM, then my desired output should be:

Duration of Events Active = 00:11:00

I have been able to aggregate the records and get a total duration (basically just EndDate - StartDate), but I cannot figure out how to exclude overlapping time. Any help would be appreciated. Thanks!

Upvotes: 1

Views: 137

Answers (1)

S3S
S3S

Reputation: 25152

How about a CTE and aggregation? This can be done with a sub-query too.

declare @table table (id int, StartDate datetime, EndDate datetime)
insert into @table
values

( 1,'2017-08-28 12:00:00 PM','2017-08-28 12:01:00 PM'),
(2,'2017-08-28 1:15:00 PM','2017-08-28 1:17:00 PM'),
(3,'2017-08-28 1:16:00 PM','2017-08-28 1:20:00 PM'),
(4,'2017-08-28 1:30:00 PM','2017-08-28 1:35:00 PM')

declare @StartDate datetime = '2017-08-28 12:00:00'
declare @EndDate datetime = '2017-08-28 14:00:00'


;with cte as(
select
    id
    ,StartDate = case when StartDate < lag(EndDate) over (order by id) then lag(EndDate) over (order by id) else StartDate end
    ,EndDate
from 
    @table
where
    StartDate >= @StartDate
and EndDate <= @EndDate),


cte2 as(
select 
     Dur = datediff(second, StartDate, EndDate)
from cte)

select
    Dur = convert(varchar, dateadd(ms, sum(Dur) * 1000, 0), 114)
from
    cte2

Upvotes: 1

Related Questions