Find Datetime Range Overlaps - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Find Datetime Range Overlaps

Author

Topic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-14 : 12:06:40

This script demonstrates queries that check datetime ranges to see if they overlap, and includes test data to verify that it covers all possible situations. This type of query is needed in many common business situations where resources have to be allocated for time ranges and cannot be allocated to two things at once. Typical needs are room scheduling, labor scheduling, and equipment scheduling.

The query logic is fairly simple, so most of the script is devoted to generating test data and running queries to verify that it produces correct results.


set nocount on

-- Declare test datetime range
declare @start	datetime
declare @end	datetime
select	@start	= '20080803',
	@end	= '20080806'

-- Table for datetimes to be used for testing
declare @t1 table ( DT	datetime not null primary key clustered )

-- Load datetimes to be used for testing
insert into @t1
select DT = convert(datetime,'20080801')	union all
select DT = convert(datetime,'20080802')	union all
select DT = convert(datetime,'20080803')	union all
select DT = convert(datetime,'20080804')	union all
select DT = convert(datetime,'20080805')	union all
select DT = convert(datetime,'20080806')	union all
select DT = convert(datetime,'20080807')	union all
select DT = convert(datetime,'20080808')
order by DT


-- Declare table to hold datetime ranges for testing
declare @t2 table (
StartDT	datetime not null,
EndDT	datetime not null,
Primary key clustered (StartDT,EndDT)
)

-- Load table of datetime ranges for testing
insert into @t2
select
	StartDT = a.DT,
	EndDT	= b.DT
from
	@t1 a cross join @t1 b
where
	-- Start datetime must be before end datetime
	a.DT < b.DT
order by
	a.DT,
	b.DT

set nocount off
print
'
*************************************************************************************
This script shows how to test datetime ranges to see if they overlap.

The datetime ranges must have a start datetime less than the end datetime.
This script it not meant for situations where the range is a single point in time.

Two ranges do not overlap if the end of one range is the same as the
start of the other range, or if the start of one range is the same as
the end of the other range.  In other words, the ranges overlap only if
they have more than one point in time in common.
*************************************************************************************

'

print 'Table of datetime ranges to test overlaps against declared range'
select
	StartDT	= convert(varchar(23),a.StartDT,121),
	EndDT	= convert(varchar(23),a.EndDT,121)
from
	@t2 a
order by
	a.StartDT,
	a.EndDt

print 'Datetime range to check for overlaps'
select
	[@start]	= convert(varchar(23),@start,121),
	[@end]		= convert(varchar(23),@end,121)


print 'Find if rows are before, after, or overlap time range'
select
	case
	when a.StartDT >= @end then 'After'
	when a.EndDt <= @start then 'Before'
	else 'Overlap'
	end,
	StartDT	= convert(varchar(23),a.StartDT,121),
	EndDT	= convert(varchar(23),a.EndDT,121)
from
	@t2 a
order by
	a.StartDT,
	a.EndDt

print 'Select rows that overlap time range'
select
	StartDT	= convert(varchar(23),a.StartDT,121),
	EndDT	= convert(varchar(23),a.EndDT,121)
from
	@t2 a
where
	a.StartDT	< @end		and
	a.EndDt		> @start
order by
	a.StartDT,
	a.EndDt

print 'Select rows that do not overlap time range'
select
	StartDT	= convert(varchar(23),a.StartDT,121),
	EndDT	= convert(varchar(23),a.EndDT,121)
from
	@t2 a
where
	a.StartDT	>= @end		or
	a.EndDt		<= @start
order by
	a.StartDT,
	a.EndDt

Script Results:



*************************************************************************************
This script shows how to test datetime ranges to see if they overlap.

The datetime ranges must have a start datetime less than the end datetime.
This script it not meant for situations where the range is a single point in time.

Two ranges do not overlap if the end of one range is the same as the
start of the other range, or if the start of one range is the same as
the end of the other range.  In other words, the ranges overlap only if
they have more than one point in time in common.
*************************************************************************************

Table of datetime ranges to test overlaps against declared range
StartDT                 EndDT                   
----------------------- ----------------------- 
2008-08-01 00:00:00.000 2008-08-02 00:00:00.000
2008-08-01 00:00:00.000 2008-08-03 00:00:00.000
2008-08-01 00:00:00.000 2008-08-04 00:00:00.000
2008-08-01 00:00:00.000 2008-08-05 00:00:00.000
2008-08-01 00:00:00.000 2008-08-06 00:00:00.000
2008-08-01 00:00:00.000 2008-08-07 00:00:00.000
2008-08-01 00:00:00.000 2008-08-08 00:00:00.000
2008-08-02 00:00:00.000 2008-08-03 00:00:00.000
2008-08-02 00:00:00.000 2008-08-04 00:00:00.000
2008-08-02 00:00:00.000 2008-08-05 00:00:00.000
2008-08-02 00:00:00.000 2008-08-06 00:00:00.000
2008-08-02 00:00:00.000 2008-08-07 00:00:00.000
2008-08-02 00:00:00.000 2008-08-08 00:00:00.000
2008-08-03 00:00:00.000 2008-08-04 00:00:00.000
2008-08-03 00:00:00.000 2008-08-05 00:00:00.000
2008-08-03 00:00:00.000 2008-08-06 00:00:00.000
2008-08-03 00:00:00.000 2008-08-07 00:00:00.000
2008-08-03 00:00:00.000 2008-08-08 00:00:00.000
2008-08-04 00:00:00.000 2008-08-05 00:00:00.000
2008-08-04 00:00:00.000 2008-08-06 00:00:00.000
2008-08-04 00:00:00.000 2008-08-07 00:00:00.000
2008-08-04 00:00:00.000 2008-08-08 00:00:00.000
2008-08-05 00:00:00.000 2008-08-06 00:00:00.000
2008-08-05 00:00:00.000 2008-08-07 00:00:00.000
2008-08-05 00:00:00.000 2008-08-08 00:00:00.000
2008-08-06 00:00:00.000 2008-08-07 00:00:00.000
2008-08-06 00:00:00.000 2008-08-08 00:00:00.000
2008-08-07 00:00:00.000 2008-08-08 00:00:00.000

(28 row(s) affected)

Datetime range to check for overlaps
@start                  @end                    
----------------------- ----------------------- 
2008-08-03 00:00:00.000 2008-08-06 00:00:00.000

(1 row(s) affected)

Find if rows are before, after, or overlap time range
        StartDT                 EndDT                   
------- ----------------------- ----------------------- 
Before  2008-08-01 00:00:00.000 2008-08-02 00:00:00.000
Before  2008-08-01 00:00:00.000 2008-08-03 00:00:00.000
Overlap 2008-08-01 00:00:00.000 2008-08-04 00:00:00.000
Overlap 2008-08-01 00:00:00.000 2008-08-05 00:00:00.000
Overlap 2008-08-01 00:00:00.000 2008-08-06 00:00:00.000
Overlap 2008-08-01 00:00:00.000 2008-08-07 00:00:00.000
Overlap 2008-08-01 00:00:00.000 2008-08-08 00:00:00.000
Before  2008-08-02 00:00:00.000 2008-08-03 00:00:00.000
Overlap 2008-08-02 00:00:00.000 2008-08-04 00:00:00.000
Overlap 2008-08-02 00:00:00.000 2008-08-05 00:00:00.000
Overlap 2008-08-02 00:00:00.000 2008-08-06 00:00:00.000
Overlap 2008-08-02 00:00:00.000 2008-08-07 00:00:00.000
Overlap 2008-08-02 00:00:00.000 2008-08-08 00:00:00.000
Overlap 2008-08-03 00:00:00.000 2008-08-04 00:00:00.000
Overlap 2008-08-03 00:00:00.000 2008-08-05 00:00:00.000
Overlap 2008-08-03 00:00:00.000 2008-08-06 00:00:00.000
Overlap 2008-08-03 00:00:00.000 2008-08-07 00:00:00.000
Overlap 2008-08-03 00:00:00.000 2008-08-08 00:00:00.000
Overlap 2008-08-04 00:00:00.000 2008-08-05 00:00:00.000
Overlap 2008-08-04 00:00:00.000 2008-08-06 00:00:00.000
Overlap 2008-08-04 00:00:00.000 2008-08-07 00:00:00.000
Overlap 2008-08-04 00:00:00.000 2008-08-08 00:00:00.000
Overlap 2008-08-05 00:00:00.000 2008-08-06 00:00:00.000
Overlap 2008-08-05 00:00:00.000 2008-08-07 00:00:00.000
Overlap 2008-08-05 00:00:00.000 2008-08-08 00:00:00.000
After   2008-08-06 00:00:00.000 2008-08-07 00:00:00.000
After   2008-08-06 00:00:00.000 2008-08-08 00:00:00.000
After   2008-08-07 00:00:00.000 2008-08-08 00:00:00.000

(28 row(s) affected)

Select rows that overlap time range
StartDT                 EndDT                   
----------------------- ----------------------- 
2008-08-01 00:00:00.000 2008-08-04 00:00:00.000
2008-08-01 00:00:00.000 2008-08-05 00:00:00.000
2008-08-01 00:00:00.000 2008-08-06 00:00:00.000
2008-08-01 00:00:00.000 2008-08-07 00:00:00.000
2008-08-01 00:00:00.000 2008-08-08 00:00:00.000
2008-08-02 00:00:00.000 2008-08-04 00:00:00.000
2008-08-02 00:00:00.000 2008-08-05 00:00:00.000
2008-08-02 00:00:00.000 2008-08-06 00:00:00.000
2008-08-02 00:00:00.000 2008-08-07 00:00:00.000
2008-08-02 00:00:00.000 2008-08-08 00:00:00.000
2008-08-03 00:00:00.000 2008-08-04 00:00:00.000
2008-08-03 00:00:00.000 2008-08-05 00:00:00.000
2008-08-03 00:00:00.000 2008-08-06 00:00:00.000
2008-08-03 00:00:00.000 2008-08-07 00:00:00.000
2008-08-03 00:00:00.000 2008-08-08 00:00:00.000
2008-08-04 00:00:00.000 2008-08-05 00:00:00.000
2008-08-04 00:00:00.000 2008-08-06 00:00:00.000
2008-08-04 00:00:00.000 2008-08-07 00:00:00.000
2008-08-04 00:00:00.000 2008-08-08 00:00:00.000
2008-08-05 00:00:00.000 2008-08-06 00:00:00.000
2008-08-05 00:00:00.000 2008-08-07 00:00:00.000
2008-08-05 00:00:00.000 2008-08-08 00:00:00.000

(22 row(s) affected)

Select rows that do not overlap time range
StartDT                 EndDT                   
----------------------- ----------------------- 
2008-08-01 00:00:00.000 2008-08-02 00:00:00.000
2008-08-01 00:00:00.000 2008-08-03 00:00:00.000
2008-08-02 00:00:00.000 2008-08-03 00:00:00.000
2008-08-06 00:00:00.000 2008-08-07 00:00:00.000
2008-08-06 00:00:00.000 2008-08-08 00:00:00.000
2008-08-07 00:00:00.000 2008-08-08 00:00:00.000

(6 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources