Help with creating a table view/query?

[pat333]

Hi,

I'm creating a database that logs the events of users on a website. Basically, as it stands, the DB consists of a single table which has three columns describing a single event which are eventID, userID and date.

What I want to do I create a derived table from that info detailing user session information within SQL (I expect within a View). This would be such that every event falls into a particular session of a particular user. A session is defined as it would normally be (i.e. a user session ends after 20mins of inactivity from that user).

So what the resulting table should consist of is the columns UserID, SessionNumber, Date and EventID. A chain of events belongs within a particular session for as long as the difference in the dates between events is never more than some time (such as 20mins). Any event after that belongs to a subsequent session number for that user.

Can anyone tell me whether it would be possible to create such a View? I have no idea how I would do this in MSSQL alone, without the use of an external script to generate the table each and every time (a lengthy procedure)..

Any ideas?

Thanks,
Pat.

 

[kaiana]

A little over my head however there is a forum at

http://www.asp.net

that deals specificly with SQL and ASP.

 

[petersJazz]

It would be more convinient if the table were loaded with sessions in the first place. Doing this recalculation is not that easy using general database syntax/procedures.

If the web application could log every new session this could be loaded and the problem would not arise.