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?

Improve company productivity with a Business Account.Sign Up

x
 
BillAn1Connect With a Mentor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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.