Re: Looking for "gaps" (from a time perspective) in data

On Oct 10, 12:04 pm, sybrandb <sybra..._at_gmail.com> wrote:
> On 10 okt, 11:53, jeremy <jeremy0..._at_gmail.com> wrote:
>
>
>
> > This is on 10gR2 standard edition.
>
> > We have a table defined like this:
>
> > txn_id
> > date_start
> > date_end
> > url_called
>
> > which contains a record for every time our application processes a
> > request from a browser. We would like to be able to spot any "gaps" in
> > the data - which would indicate a period when the service was
> > unavailable for whatever reason.
>
> > To do this we need to do a comparison between the date_start of each
> > row and the one that preceded (or followed)  it and then see if the
> > period of time is greater than a to-be-determined threshold.
>
> > Can a single SQL statement be constructed which could achieve this? if
> > so could you point me in the right direction? For those who haven't
> > followed what I was asking about, if you are in the majority then
> > please accept my apologies for not being more concise :)
>
> > regards,
>
> > --
> > jeremy
>
> Please refer to the LAG function in the Analytical Functions chapter
> in the Datawarehousing documentation for the version you don't care to
> mention.

You mean where I stated 10gR2 up there ^^^^?

LAG and LEAD were exactly the functions I was looking for.

> Please apologize for being *too* concise. At a minimum you should
> always post your 4 digit version number.

Blimey I didn't realise the rules now included that level of detail.

> If you can't be bother to do it, please consider not posting at all.
> Crystal balls have been worn out by similar lazy dudes like you.

Heh - I know you've been flamed before for being rude, but I'll not rise to the bait.

Thanks for your help, the pointer to the relevant functions was all I needed.

--
jeremy