I don't know but I love the subject. If he is having trouble finding a date
he has to lower his standards.
Jim
"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
news:3DB81242.4FCC4827_at_exesolutions.com...
> Karsten Farrell wrote:
>
> > XaFTek wrote:
> > > Hi !
> > >
> > > I have a table named
> > > checklog
> > > (
> > > checklogdate date not null,
> > > idapp integer not null
> > > )
> > >
> > > By example for idapp=10
> > > I have thoses lines in the table (date format is DD/MM/YY):
> > > 20/10/02 10
> > > 12/10/02 10
> > > 10/10/02 10
> > > 09/10/02 10
> > > 06/08/02 10
> > > 05/08/02 10
> > > 04/08/02 10
> > > 02/08/02 10
> > > 01/08/02 10
> > > 30/06/02 10
> > >
> > > There is only 1 line by day for an idapp.
> > >
> > > Starting down from the date 12/10/02 I want to know when my checklog
> > > "start record".
> > > Start record is where checklog began to record my idapp within a 7
> > > days no recording gap.
> > >
> > > For this example I have a session recording form 09/10/02 to 12/10/02
> > > because there is more than 7 days between 09/10/02 and 06/08/02.
> > > I have also a session recording from 06/08/02 to 01/08/02.
> > >
> > > How can I do that in SQL ?
> > >
> > > Thank's in advance.
> >
> > If I understand your question correctly, it's pretty tough in regular
> > SQL because you need to compare the date in the previous row fetched
> > with the date in the current row. Usually when I run into this kind of
> > problem, I revert to PL/SQL.
> >
> > Are you asking the SQL to do something like this pseudocode?
> >
> > fetch first row where idapp = 10
> > save checklogdate as break_checklogdate
> > loop
> > fetch next row where idapp = 10; exit when no more rows
> > if (cklogdate - break_checklogdate) > 7
> > found_a_start_record_so_do_something
> > save checklogdate as break_checklogdate
> > end if
> > end loop
> > process final break_checklogdate
>
> If you are correct as to what XaFTek is asking. Isn't XaFTek making a
> horribly invalid assumption that the row fetched before the current row is
> predictable? Oracle makes no such guarantee.
>
> Daniel Morgan
>
Received on Thu Oct 24 2002 - 22:04:54 CDT
