asked on

How to query start and end times in two separate tables to determine if there is any overlap.

I have two separate tables. Each table has a start_time and and end_time field. They are all in DateTime format. I want to find a way to determine whether these two time periods have any overlap.

Example:

tbl1.start_time = 11:30 a.m.
tbl1.end_tme1 = 12:30 p.m.

tbl2.start_time2 = 11:00 a.m.
tbl2.end_time2 = 1:00 p.m.

In this example the result would be true.

Thanks in advance for your help.

BillAn1

is there only 1 record in each table?
what is the significance of the 1/2 suffixes on the time stamps?

if the simplest case is true, that there is only 1 record in each, then

select
case
when tbl1.start_time between tbl2.start_time and tbl2.end_time then 'OVERLAP'
when tbl1.end_time between tbl2.start_time and tbl2.end_time then 'OVERLAP'
when tbl2.start_time between tbl1.start_time and tbl1.end_time then 'OVERLAP'
when tbl2.end_time between tbl1.start_time and tbl1.end_time then 'OVERLAP'
else 'NO_OVERLAP'
end
as result
from table1, table2

robbid

ASKER

There are multiple records in each table.
You can disregard the suffix on the end of hte field names. I was just trying to indicate that they are separate fields in separate tables.

I have two sets of records with start and end times and as I process a particular record from table one, I want to query table two to determine if it contains any records where there was any overlap with the table one.

There could be multiple overlaps, but right now I don't care if I determine how many overlaps there are.

I hope this makes it more clear.

ASKER CERTIFIED SOLUTION

BillAn1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

waelothman

try this

select tbl1.TID from tbl1 Inner join tbl2 on (
tbl1.start_time between tbl2.start_time and tbl2.end_time or
tbl1.end_time between tbl2.start_time and tbl2.end_time or
tbl2.start_time between tbl1.start_time and tbl1.end_time or
tbl2.end_time between tbl1.start_time and tbl1.end_time )

waelothman

TID is any field you want to see

robbid

ASKER

BillAn1,
I'm sorry about not being clear. The words aren't coming out quite as clear as the thought is in my head. Anyway, what you gave me looks great -- exactly what I am looking for, however, I get an error: "Syntax Error or Access Violation".

I have converted what you have given me to meet my true table and field names. Can you look at what I have below and let me know if I am missing something? Thanks.

----------------------Start SQL (formatting, spacing by VS.NET)-------------------------------
SELECT TOP 1 result
FROM (SELECT CASE WHEN @start BETWEEN tbl_21CMsgRtg.EntryDateTime AND
tbl_21CMsgRtg.RemoveDateTime THEN 'OVERLAP' WHEN @end BETWEEN tbl_21CMsgRtg.EntryDateTime AND
tbl_21CMsgRtg.EntryDateTime THEN 'OVERLAP' WHEN tbl_21CMsgRtg.EntryDateTime BETWEEN @start AND
@end THEN 'OVERLAP' WHEN tbl_21CMsgRtg.RemoveDateTime BETWEEN @start AND
@end THEN 'OVERLAP' ELSE 'NO_OVERLAP' END AS result
FROM tbl_21CMsgRtg) a
ORDER BY result DESC
----------------------End SQL-------------------------------

BillAn1

I created a table and ran your SQL without any problems, how are you declaring / initialising the variables etc?

this i what I ran in Query Analyser, and it ran fine :

create table tbl_21CMsgRtg(EntryDateTime datetime, RemoveDateTime datetime)
insert into tbl_21CMsgRtg values ('2001-01-01','2001-02-01')

declare @start datetime
declare @end datetime

set @start = '2001-01-21'
set @end = '2001-02-21'

SELECT TOP 1 result
FROM (SELECT CASE WHEN @start BETWEEN tbl_21CMsgRtg.EntryDateTime AND
tbl_21CMsgRtg.RemoveDateTime THEN 'OVERLAP' WHEN @end BETWEEN tbl_21CMsgRtg.EntryDateTime AND
tbl_21CMsgRtg.EntryDateTime THEN 'OVERLAP' WHEN tbl_21CMsgRtg.EntryDateTime BETWEEN @start AND
@end THEN 'OVERLAP' WHEN tbl_21CMsgRtg.RemoveDateTime BETWEEN @start AND
@end THEN 'OVERLAP' ELSE 'NO_OVERLAP' END AS result
FROM tbl_21CMsgRtg) a
ORDER BY result DESC