Pull overlapping fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pull overlapping fields

Author

Topic

amber
Starting Member

7 Posts

Posted - 2008-04-09 : 16:00:12

Hello,
How can I create a query that pulls all records for a given ID (road) where the distances (based on start & end fields) overlap?

Eg. RoadNm Start End
Road 1 0 500
Road 1 300 800
Road 2 0 500
Road 2 500 800

I need to write SQL that flags Road 1 as a road with overlapping sections (whereas Road 2 is fine).

Thanks!
Amber

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-09 : 16:12:17

Edit: haven't tested it...

select *
from table t1
where exists (select * from table t2 where t2.roudNm = t1.roudNm and t1.end > t2.start)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-09 : 16:32:39

this should do:


declare @t1 table (RoadNm varchar(10), Start int, End1 int)
insert into @t1
select 'Road 1', 0, 500 union all
select 'Road 1', 300, 800 union all
select 'Road 2', 0, 500 union all
select 'Road 2', 500, 800

select	RoadNm
from	@t1 
group by RoadNm
having	sum(end1 - start) != MIN(start) + MAX(end1)

EDIT: fixed a having

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

amber
Starting Member

7 Posts

Posted - 2008-04-09 : 17:50:50

Yep - that did it.
Thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources