Finding gaps in a series of time periods

1

I am trying to find gaps in a series of start and end times, for example:

DECLARE @times TABLE (
  Starttime time,
  EndTime time
) 
INSERT INTO @times VALUES
  ('7:00', '8:15'), ('7:15','7:24'), ('8:16','8:26')
  ,('8:25','8:27'), ('8:22','8:34'), ('8:39','9:00')

I tried selecting the end times and joining to start times that are later, but that only seems to work if there is no overlap. In the example above I am looking for the gaps between 8:15 and 8:16 (first entry end time and third entry start time), and 8:34 to 8:39.

2

hi one idea is to put

starttime and endtime in on single row and do order by

select * from 
(
	select   Starttime from @times 
		union all 
	select   Endtime from @times 
) a 
order by a.Starttime

image
image352×539 6.15 KB

3

You can use LEAD to get the 'next' Starttime and compare to the 'current' EndTime:

 Select Starttime
      , EndTime
      , iif(lead(Starttime) over(Order By Endtime) > EndTime, 1, 0)
   From @times

There are possibly better ways to accomplish your goal - but that would require additional information on the problem you are trying to solve.

4

Ok, after a good nights rest I came up with the following, it's a little crazy, but does the trick so far, just could probably be a LOT cleaner lol.

if object_id('tempdb..#temp') is not null drop table #temp

if object_id('tempdb..#temp2') is not null drop table #temp2

 

declare @temp table ( start  time, [end]  time );

insert into @temp values

('7:00','8:15'), ('7:15','7:24'),('8:16','8:26')

,('8:25','8:27'), ('8:22','8:34'),('8:39','9:00');

 

select distinct a.[Start], isnull(bEnd,a.[End]) as 'End' into #temp from @temp a

outer apply(select top 1 b.[end] as 'bEnd' from @temp b where b.start between a.start and a.[end] and b.[end] > a.[end] order by b.[end] desc) b

order by 1

 

select distinct a.* into #temp2 from #temp a

left join #temp b

on b.[Start] <= a.[Start] and b.[End] >= a.[End]

and not (b.[Start] = a.[Start] and b.[End] = a.[End])

where b.[Start] is null

 

select distinct End_log.[End], Start_log.Start

,DATEDIFF(minute, End_log.[End], Start_log.Start) 'Diff'

from #temp2 as End_log

outer apply (

       select top 1 * from #temp2 as Start_log

       where start_log.Start > End_log.[End]

) Start_log

order by 1
5

Why all of that? Did you look at the solution I provided - no need for multiple distincts or using isnull or outer apply...

6

Try generating the number of concurrent times. You can then select when Concurrent = 0.

WITH TimeAdd
AS
(
	SELECT IIF(N = 1, Starttime, Endtime) AS TimePoint
		,N.N
	FROM @times T
		CROSS APPLY (VALUES(1), (-1)) N (N)
)
,TimeOverLap
AS
(
	SELECT TimePoint
		,LEAD(TimePoint) OVER (ORDER BY TimePoint) AS NextTimePoint
		,SUM(N) OVER (ORDER BY TimePoint ROWS UNBOUNDED PRECEDING) AS Concurrent
	FROM TimeAdd
)
SELECT TimePoint, NextTimePoint
FROM TimeOverLap
WHERE Concurrent = 0
	AND NextTimePoint IS NOT NULL
ORDER BY TimePoint;
7

Your totally right, I should have read that sooner and saved myself some trouble, lol. Would there be a way to count up the lines to a gap?

DECLARE @times TABLE (
  Starttime time,
  EndTime time
) 
INSERT INTO @times VALUES
  ('7:00', '8:15'), ('7:15','7:24'), ('8:16','8:26')
  ,('8:25','8:27'), ('8:22','8:34'), ('8:39','9:00')

Select Starttime, EndTime
	,Datediff(minute, starttime, endtime) as 'Duration'
	,iif(lead(Starttime) over(Order By Endtime) > EndTime, 1, 0) 'Gap'
From @times
Starttime EndTime Duration Gap
07:15:00.0000000 07:24:00.0000000 9 0
07:00:00.0000000 08:15:00.0000000 75 1
08:16:00.0000000 08:26:00.0000000 10 0
08:25:00.0000000 08:27:00.0000000 2 0
08:22:00.0000000 08:34:00.0000000 12 1
08:39:00.0000000 09:00:00.0000000 21 0

So for instance 9+75, then 10+2+12

I recall being able to increment a variable in a select statement, so I was thinking it might be possible to do that when the gap is found since it returns a 1, then I could sum them. Not sure if that's possible though, or if I am going the wrong way.

8

hi

please see this idea .. take the sum for each grouping .. 1+1 2+2 3+3

select 
      ((ROW_NUMBER() over (ORDER BY endtime) - 1)  / 2) +1 as ChunkNo  
      , * 
from 
  @times

image