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.
robbidAsked:
Who is Participating?
 
BillAn1Commented:
nope, this make it less clear!
"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.  "
doe this mean that you are in a pice of TSQL, with 2 variables which can be passed into a query? or what exaclty do you mean by ...as I process a particular record.....
if you have 2 variables, say @start and @end
then you can query table2 in many ways, e.g.

select result, count(*) as total from (
select
case
when @start between tbl2.start_time and tbl2.end_time then 'OVERLAP'
when @end between tbl2.start_time and tbl2.end_time then 'OVERLAP'
when tbl2.start_time between @start and @end then 'OVERLAP'
when tbl2.end_time between @start and @end then 'OVERLAP'
else 'NO_OVERLAP'
end
as result
from table2
) a group by result
- this will give you a count of how many overlap, and how many do not

or


select top 1 result from (
select
case
when @start between tbl2.start_time and tbl2.end_time then 'OVERLAP'
when @end between tbl2.start_time and tbl2.end_time then 'OVERLAP'
when tbl2.start_time between @start and @end then 'OVERLAP'
when tbl2.end_time between @start and @end then 'OVERLAP'
else 'NO_OVERLAP'
end
as result
from tbl2
) a order by result desc
-- will just give you OVERALP if there are any overlaps, else NO_OVERLAP as a single record








0
 
BillAn1Commented:
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

0
 
robbidAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
waelothmanCommented:
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  )




0
 
waelothmanCommented:
TID is any field you want to see
0
 
robbidAuthor Commented:
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-------------------------------
0
 
BillAn1Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.