Re: Finding a date

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 - 10:31:35 CDT