SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Find Datetime Range Overlaps
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/14/2008 :  12:06:40  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000