Killer Query

[shauns1]

Hi

I'm hoping someone could give me a hand with what seems to be an impossible task.

I have a table of website hits. Each record contains the following:

HitID
CreationDate
IPAddress
DevelopmentID

All are self explainatory apart from DevelopmentID. Basically, the site is a portal that leads into different Building Development Projects. Each one a site in its own right. Each development has it's own client who can login and see visitor numbers for his site.

I have built a counter that records new IP Addresses if they haven't been recorded within a given amount of time (2hrs). This means that any one visitor can not log more than one session per 2 hrs. The counter also records the DevelopmentID that they are viewing. This is the problem with the current system. The developmentID is held in a Session variable called DevelopmentID. The same SessionVariable is used for all of the developments. (This can not be changed!)

My problem is that when I log a Session along with the DevelopmentID, if that user then goes on to view another development, their session will remain logged (within the 2 hrs) under the previous DevelopmentID.

So, I wondered if I could just log every 'hit' along with the current DevelopmentID and then query my hits table to work out individual sessions?

I need to select all IPAdresses that have viewed a particular development BUT whose creation date was not within 2 hrs of another hit with the same IP Address. Thus breaking the results down into 2 hour sessions.

My question is, how do you iterate through the table checking to see if CreationDate is within two hours of any other CreationDate with a matching IPAddress?

Sorry about the long post!

Many thanks

Shaun

 

[lynchg]

Try this:

Select wh1.HitID, wh1.IPAddress, wh1.CreationDate, wh1.DevelopmentID, wh2.IPAddress, wh2.CreationDate
From WebHits as wh1
Inner Join WebHits as wh2
On wh1.IPAddress = wh2.IPAddress
Where DateDiff("h",wh1.CreationDate,wh2.CreationDate) < 2

 

[shauns1]

You make it look sooo easy. I think this does the job for now. I will work with it over the next few days to test.

Many thanks for your help!

Shaun