count records outside a 2 hour timespan 2

[Painkiller]

Hi all,

I have the following problem: we have a datbase where the ip adresses of the people visiting our website are stored, together with the time at which the website is visited. Table looks like this:

ID Date IP
1 2003-02-13 09:41:48.463 194.151.67.130
2 2003-02-13 09:41:54.383 194.151.67.130
3 2003-02-13 09:42:47.043 195.121.73.78
5 2003-02-13 09:54:28.993 212.61.38.106
6 2003-02-13 09:56:00.840 212.61.38.106
7 2003-02-13 09:56:36.120 212.61.38.106

I need to write a stored procedure that returns the counts of the visits per IP-adress, but so that consequetive visits from an IP-adress within 2 hours are only counted as 1 visit. The last part of this problem has got me stumped. Anybody know a way to tackle this problem?

 

[nealvenables]

Tricky one.

I've had a play around with this, and I don't think you can discriminate your hits into two-hour windows that are strictly relative to each other (at least not without doing a lot of cursoring).

However, the following method breaks each day into twelve two-hour bands and then counts multiple hits to an IP address within each band only once.


Create Table #HitCount (IP varchar(50), Timeband varchar(50))

Set NOCOUNT On

Insert #HitCount (IP, Timeband)
Select IP, left([date],11) + '-' + cast(datepart(hh,[Date])/2 as char(1))
From Table1
Group By IP, left([date],11) + '-' + cast(datepart(hh,[Date])/2 as char(1))

Select IP, count(IP) as Hits
From #HitCount
Group By IP

Drop Table #HitCount

 

[nealvenables]

Alternatively, you could write a trigger that

1. sets a timestamp against each IP address that is written to your table.

2. has a rule that says "do not record hits from an IP address unless 2+ hours have elapsed since its most recent timestamp".

This way your hits table store the results you want without any clever querying.

 

[Painkiller]

Thanx, the last idea seems to be the best. I'll give that a try.