Re: Impossible SQL query?

Hi!
I can't see an solution that not includes some procedual logic here (e.g. use of pl/sql, perl-script, java, etc)
Probably you thought about an solution, where you loop through for all remembered start-dates:
 select min(end_date) from a where keycode='E' and end_date > last_remembered_startdate

Greetings
Bjørn

<vincentj_at_nycap.rr.com> skrev i en meddelelse news:1128364984.700710.207010_at_g44g2000cwa.googlegroups.com...
> Hi, I'm trying to work out this query, I've been racking my brain over
> this for weeks and I just can't see the solution. I'm hoping someone
> here will be able to see things differently than me.
>
> I've got these data points that mark the beginning and end of a
> process, lets call them B (begin) and E (end.) The problem is that
> there is nothing that links the begin point to the end point, except a
> date stamp. End points always come after begin points. There could be
> more than one end point, in which case we take the first one and
> discard the rest. So we could have data like this:
>
>
> ID Key Date
> -- --- ------
> 1 B 1/1/05
> 2 B 1/2/05
> 3 E 1/3/05
> 4 B 1/3/05
> 5 E 1/4/05
> 6 E 1/4/05
> 7 E 1/5/05
> 8 B 1/6/05
> 9 B 1/7/05
> 10 E 1/7/05
>
>
> The result would look something like this:
>
>
> Begin End
> ----- ---
> B1 E3
> B2 E5
> B4 E6
> B8 E10
> B9
>
>
> Notice that E7 gets discarded because there are not enough begin points
> before it. Also notice that B9 does not have an end point. We can
> discard end points but never begin points.
>
> Does anyone think they can tackle this query? It can use any advanced
> SQL functions (subqueries, analytics, etc.) available in Oracle
> 8.1.7.4, but I don't want to use PL/SQL. The output doesn't have to
> look exactly like that, I'm just looking for the logic. Thanks, I
> really appreciate any help I can get here. Or if this is impossible
> without PL/SQL, please let me know! Thanks!
>
Received on Mon Oct 03 2005 - 15:07:46 CDT