Hello SQL-experts,

I have a table with a datetime field, 'accesstime'. The table also
holds a varchar column, 'IPaddress". I now need to select all the IP
addresses of this table where this datetime field is not represented
within 3 hours of another, similar entry.

Here's what I've got:


This is my table:

CREATE TABLE [dbo].[OlapWebUseLogEntries](
[PresentationID] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL,
[AccessTime] [datetime] NOT NULL,
[IPAddress] [bigint] NOT NULL,
[PageCalled] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_testTable] PRIMARY KEY CLUSTERED
(
[PresentationID] ASC,
[AccessTime] ASC,
[IPAddress] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Example of table content: (the bigint is the IP-address)

PresentationNo1;15-04-2008 15:17:28;2130706433;foo.aspx
PresentationNo1;15-04-2008 15:17:38;2130706433;foo.aspx
PresentationNo1;15-04-2008 15:17:41;2130706433;foo.aspx
PresentationNo1;15-04-2008 15:17:43;2130706433;foo2.aspx
PresentationNo2;15-04-2008 15:25:48;2195982562;foo3.aspx
PresentationNo2;15-04-2008 15:27:46;2195982562;foo2.aspx
PresentationNo1;15-04-2008 15:29:35;2195982562;foo3.aspx
PresentationNo1;15-04-2008 15:30:09;2195982562;foo.aspx
PresentationNo1;15-04-2008 15:53:41;2195982548;foo.aspx
PresentationNo1;15-04-2008 15:53:44;2195982548;foo.aspx


So in the above, I would like to select the first case, but not the
second and not the third - as the difference in datetime value to the
first is less than three hours to the first. Case no. four is also fine,
as its 'PageCalled' value is different from the first two. And so on and
so forth.

My best try was the below:


SELECT PresentationID, AccessTime, IPAddress, PageCalled
FROM OlapWebUseLogEntries AS foo
WHERE (AccessTime > '14-04-2008')
AND (AccessTime < '16-04-2008')
AND ( NOT EXISTS
(SELECT PresentationID, AccessTime, IPAddress, PageCalled
FROM OlapWebUseLogEntries AS bar
WHERE (PresentationID = foo.PresentationID) AND (IPAddress
= foo.IPAddress) AND (foo.PageCalled = PageCalled) AND
(DATEDIFF(hh, AccessTime, foo.AccessTime) < 3)))
ORDER BY AccessTime


Alas this doesn't seem the right way to go. Any clues on how to go about
this would surely be appreciated. Thank a lot in advance,

best,

Morten

Here is one way (SQL Server 2005):

;WITH RankedLogEntries
AS
(SELECT PresentationID, AccessTime, IPAddress, PageCalled,
ROW_NUMBER() OVER(
PARTITION BY PresentationID, IPAddress, PageCalled
ORDER BY AccessTime) AS seq
FROM OlapWebUseLogEntries)
SELECT N.PresentationID, N.AccessTime, N.IPAddress, N.PageCalled
FROM RankedLogEntries AS N
LEFT JOIN RankedLogEntries AS P
ON N.seq = P.seq + 1
AND N.PresentationID = P.PresentationID
AND N.IPAddress = P.IPAddress
AND N.PageCalled = P.PageCalled
WHERE N.AccessTime > '20080414'
AND N.AccessTime < '20080416'
AND DATEDIFF(hh, COALESCE(P.AccessTime, '19000101'),
N.AccessTime) >= 3;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Sir,

you just blew me away with that one, that's was amazing! You're a goto
guy, that's what you are,

thanks a million,

Morten


Plamen Ratchev skrev:
> Here is one way (SQL Server 2005):
>
> ;WITH RankedLogEntries
> AS
> (SELECT PresentationID, AccessTime, IPAddress, PageCalled,
> ROW_NUMBER() OVER(
> PARTITION BY PresentationID, IPAddress, PageCalled
> ORDER BY AccessTime) AS seq
> FROM OlapWebUseLogEntries)
> SELECT N.PresentationID, N.AccessTime, N.IPAddress, N.PageCalled
> FROM RankedLogEntries AS N
> LEFT JOIN RankedLogEntries AS P
> ON N.seq = P.seq + 1
> AND N.PresentationID = P.PresentationID
> AND N.IPAddress = P.IPAddress
> AND N.PageCalled = P.PageCalled
> WHERE N.AccessTime > '20080414' AND N.AccessTime < '20080416'
> AND DATEDIFF(hh, COALESCE(P.AccessTime, '19000101'),
> N.AccessTime) >= 3;
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com