Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Detecting Date ranges that overlap

Posted on 2007-08-03
7
Medium Priority
?
1,237 Views
Last Modified: 2013-11-05
Hi

I have a table that contains startDate and endDate for example

Id   StartDate          End Date
55      2007-08-01      2007-08-14
56      2007-08-21      2007-08-28
57      2007-09-01      2007-09-12

I want to pass a date range and return the Id of any lines that conflict.
eg
spConflictingDates (2007-08-10, 2007,2007-08-22) would return 55 & 56
spConflictingDates (2007-08-27, 2007,2007-08-30) would return 56

Any help would be greatly recieved

Thanks


Rob
0
Comment
Question by:langlro1
  • 3
  • 2
  • 2
7 Comments
 
LVL 21

Expert Comment

by:ziolko
ID: 19623642
create procedure conflictdates
@fromdate
@todate
as
select
 Id
from table_name
where (startDate between fromdate and todate ) or (enddate between fromdate and todate)

ziolko.
0
 
LVL 22

Expert Comment

by:dportas
ID: 19623789
Ziolko's solution does not quite capture every case of overlapping date ranges, although it works for the examples given. Try the same query where @fromdate = '20070822' and @todate = '20070827'.

Here's my solution:

SELECT id
FROM table_name
WHERE StartDate <= @todate
 AND EndDate >= @fromdate;

Notice that I've assumed an event that ends on @fromdate is still counted as overlapping. If that's not what is required then change the final >= to >.
0
 
LVL 21

Expert Comment

by:ziolko
ID: 19623852
dportas > it depends what he means overlapping dates:) but it's my mistake should ask for more details.

ziolko.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:langlro1
ID: 19624343
Thank you both for your comments, I am going to have a play over the next few hours....
0
 
LVL 1

Author Comment

by:langlro1
ID: 19625194
Okay, the problem with dportas solutions is that if I use a wide range of dates no record are returned for example
spConflictingDates (2007-08-01, 2007,2007-09-02) should return 55, 56 & 57

ziolko result does return multiple records which is what I want, however I want to exclude the given toDate and fromDate

For Example

spConflictingDates (2007-08-14, 2007,2007-08-21) would return NO records

Thanks for your help so far

Rob

0
 
LVL 22

Accepted Solution

by:
dportas earned 2000 total points
ID: 19625339
I think you must have done something different to what I suggested but try this:

CREATE TABLE table_name (id INT NOT NULL PRIMARY KEY, StartDate DATETIME NOT NULL, EndDate DATETIME NOT NULL);
INSERT INTO table_name VALUES (55, '20070801', '20070814');
INSERT INTO table_name VALUES (56, '20070821', '20070828');
INSERT INTO table_name VALUES (57, '20070901', '20070912');

DECLARE @fromdate DATETIME,@todate DATETIME;

SELECT @fromdate = '20070801', @todate = '20070902';

SELECT id
FROM table_name
WHERE StartDate < @todate
 AND EndDate > @fromdate;

SELECT @fromdate = '20070814', @todate = '20070821';

SELECT id
FROM table_name
WHERE StartDate < @todate
 AND EndDate > @fromdate;

Result:

id
-----------
55
56
57

(3 row(s) affected)

id
-----------

(0 row(s) affected)
0
 
LVL 1

Author Comment

by:langlro1
ID: 19625523
dportas Thanks... I must of entered the dates the wrong way round or something.  I have retested the various scenerios and it works.

thanks again

Rob
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

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
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

577 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