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
335 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert varchar UTC to human datetime 1 38
Import export tables 5 21
Using this function 4 38
SQL Server Express automatically execute SQL or SP 8 27
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

679 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