Path: text.usenetserver.com!out02b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!t39g2000prh.googlegroups.com!not-for-mail From: jeremy Newsgroups: comp.databases.oracle.server Subject: Re: Looking for "gaps" (from a time perspective) in data Date: Fri, 10 Oct 2008 05:52:24 -0700 (PDT) Organization: http://groups.google.com Lines: 56 Message-ID: <1021bbe2-2588-4a93-aa0c-e9435e1d3f9b@t39g2000prh.googlegroups.com> References: <45baec79-b606-4198-8912-52af5632e9a3@m3g2000hsc.googlegroups.com> NNTP-Posting-Host: 193.164.109.10 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1223643144 20428 127.0.0.1 (10 Oct 2008 12:52:24 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 10 Oct 2008 12:52:24 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: t39g2000prh.googlegroups.com; posting-host=193.164.109.10; posting-account=h5IA6woAAABcHbb4vAIZFk2TfZkZ_eqe User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.9.0.3) Gecko/2008092417 Firefox/3.0.3,gzip(gfe),gzip(gfe) Xref: usenetserver.com comp.databases.oracle.server:448563 X-Received-Date: Fri, 10 Oct 2008 08:52:25 EDT (text.usenetserver.com) On Oct 10, 12:04=A0pm, sybrandb wrote: > On 10 okt, 11:53, jeremy 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) =A0it 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