Solved

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

Posted on 2004-10-13
7
338 Views
Last Modified: 2006-11-17
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.
0
Comment
Question by:robbid
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 17

Expert Comment

by:BillAn1
ID: 12301646
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
 

Author Comment

by:robbid
ID: 12301743
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
 
LVL 17

Accepted Solution

by:
BillAn1 earned 500 total points
ID: 12302030
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:waelothman
ID: 12302114
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
 
LVL 5

Expert Comment

by:waelothman
ID: 12302119
TID is any field you want to see
0
 

Author Comment

by:robbid
ID: 12303365
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 12306254
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

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question