Re: SQL: Working with huge tables of chronological data

Thanks for your answers, here is a clarification:

• Ax and Ay are any different A events (A1, A2...). But since I'm looking for chronological sequences, Ay has to happen after Ax.
• If I merge the two tables A and B while respecting the chronology, this would lead to the following. I've also add an additionnal A4 event here to clarify.

Table A

(03:50pm, A1)
(03:55pm, A2)
(03:58pm, A3)
(03:59pm, A4)

Table B
(03:51pm, B1)
(04:00pm, B2)

Chronology

(03:50pm, A1)
(03:51pm, B1)
(03:55pm, A2)
(03:58pm, A3)
(03:59pm, A4)
(04:00pm, B2)

I'm looking for all the sequences of events A in the chronology with no B event in the middle. Here the results would be: (A2, A3) ; (A2, A4) and (A3, A4)

Thanks!

John

On Apr 5, 10:59 am, DA Morgan <damor..._at_psoug.org> wrote:
> John wrote:
> > Hi,
>
> > I'm trying to figure out an efficient way to search for the non
> > existence of events in chronological data with SQL. The goal (detailed
> > below) seems pretty simple but so far it looks like it's tricky to do
> > with Oracle. Here's my problem:
>
> > I'm working with 2 simple but huge tables each recording a different
> > kind of event associated with a timestamp. For instance:
>
> > Table A
> > (03:50pm, A1)
> > (03:55pm, A2)
> > (03:58pm, A3)
>
> > Table B
> > (03:51pm, B1)
> > (04:00pm, B2)
>
> > I'm looking for all the chronological sequences (Ax, Ay) where no B
> > event is present between Ax and Ay. In this example, the result would
> > be (A2, A3).
>
> > I've been searching actively for an efficient solution for this
> > problem and I couldn't find any fast enough. Do you have any idea?
>
> > Thanks a lot,
>
> > John
>
> To me your example and explantion make no sense.
>
> What Ax and Ay? Please try for more clarity with your explanation.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -
Received on Thu Apr 05 2007 - 10:29:18 CDT