Path: text.usenetserver.com!out04b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!m3g2000hsc.googlegroups.com!not-for-mail From: sybrandb Newsgroups: comp.databases.oracle.server Subject: Re: Looking for "gaps" (from a time perspective) in data Date: Fri, 10 Oct 2008 04:04:16 -0700 (PDT) Organization: http://groups.google.com Lines: 40 Message-ID: <45baec79-b606-4198-8912-52af5632e9a3@m3g2000hsc.googlegroups.com> References: NNTP-Posting-Host: 195.193.209.18 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1223636657 31211 127.0.0.1 (10 Oct 2008 11:04:17 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 10 Oct 2008 11:04:17 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: m3g2000hsc.googlegroups.com; posting-host=195.193.209.18; posting-account=tT5rmQoAAACCqzB1CIW5p92n51ZWKb0D User-Agent: G2/1.0 X-HTTP-Via: 1.1 WALWPROXYPRD X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729),gzip(gfe),gzip(gfe) Xref: usenetserver.com comp.databases.oracle.server:448555 X-Received-Date: Fri, 10 Oct 2008 07:04:17 EDT (text.usenetserver.com) 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. Please apologize for being *too* concise. At a minimum you should always post your 4 digit version number. 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. -- Sybrand Bakker Senior Oracle DBA