Re: Finding a date

From: Karsten Farrell <kfarrell_at_medimpact.com>
Date: Thu, 24 Oct 2002 00:03:38 GMT
Message-ID: <uNGt9.80$C13.9113739@newssvr13.news.prodigy.com>

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 Received on Wed Oct 23 2002 - 19:03:38 CDT