Ch #6 - Crossing Intervals - 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 2000 Forums

Transact-SQL (2000)

Ch #6 - Crossing Intervals

Author

Topic

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-12-02 : 15:48:57

[code]create table #t (a int, b int)
insert into #t
select 1, 20 union all
select 9, 17 union all
select 3, 6 union all
select 33, 44 union all
select 45, 48 union all
select 55, 66 union all
select 59, 62[/code]
Result should be:[code]
aa bb
-------- --------
1 20
33 44
45 48
55 66[/code]
I.e., find left and right boundaries of each union of crossing intervals..

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2003-12-02 : 15:51:21

Is this homework you're posting?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-12-02 : 16:05:43

No!! I just write a book on SQL and need nice solutions.. :)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-02 : 16:41:43

[code]

select a,b
from
(
select a,b,(select count(*) from #t b where b.a <= a.a and b.b >= a.b) as boundary
from #t a
) a
where a.boundary = 1
[/code]

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-12-02 : 17:25:07

Very smart, Jay..

.... and this is mine:

select min(a), bb from
(select a, (select max(b) from #t tt where not(tt.a>t.b)) bb
from #t t) z group by bb

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2003-12-02 : 17:57:22

And in this book you're going to credit all the people that wrote the queries you're publishing right?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-12-02 : 21:12:09

I think Ken Henderson already wrote this book

Damian

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-02 : 21:29:30