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
322 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

832 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