is this possible? obtain the min and max of rows with discrete intervals?

[Mike]

I'm more unsure of myself these days:
Given a result set of rows with discrete date intevals; but rows may
be non contiguous in nature; Can a simple, non cursor driven query
produce a result with the min and max date intervals?
eg.
the result table R has the following rows
key1|level1|05/01/01|10/01/02|
key1|level1|01/01/01|04/30/01|
key1|level1|11/10/00|12/31/00|
key1|level1|06/01/00|10/09/00|
key1|level1|01/01/00|05/30/00|

**** some sql **** results in

key1|level1|11/10/00|10/01/02|
key1|level1|01/01/00|10/09/00|

Does this need recursion?
Mike

[Ian]

Mike wrote:

I'm more unsure of myself these days:
Given a result set of rows with discrete date intevals; but rows may
be non contiguous in nature; Can a simple, non cursor driven query
produce a result with the min and max date intervals?
eg.
the result table R has the following rows
key1|level1|05/01/01|10/01/02|
key1|level1|01/01/01|04/30/01|
key1|level1|11/10/00|12/31/00|
key1|level1|06/01/00|10/09/00|
key1|level1|01/01/00|05/30/00|

**** some sql **** results in

key1|level1|11/10/00|10/01/02|
key1|level1|01/01/00|10/09/00|

Does this need recursion?
Mike

The question is, what defines the "discrete" intervals? In your
example, it looks like the first 3 rows are in one interval, and
the last 2 rows are in the other.

The only definition of the intervals I can see is that column3 is
either >= 11/10/00 or not. But how many intervals do you need?
With just 2 intervals as above, the query is simple; but if you
have a LOT of intervals the query gets complex very quickly (as the
intervals would be coded in the SQL statement).


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Mike]

Ian,
Thanks... the actual number of intervals vary and can be quite a lot
if measured over years.
The key1 ( really an identifier) and the level1 are pulled from a
table where
distinct changes in time to another parameter (not shown or required
in the result set) produces the multi rows as shown in the example.
The result set I want is then the one, two, or more rows that would
show definite, contiguous intervals (given that no two rows
(intervals) can contain the same day) and each new row's start has to
be one day after the last row's end date. Hope that clears up your
first question.
I have been eyeing recursion or trees but wanted to know if this is
one of those questions that can't be answered without creating an
application. I also have a habit of complicating something, and I
wanted to get someone to agree that it is complex.
Thanks for your help,
Mike