A view involving complicated date calculations. 2

[raze]

Hi there ... <br>
<br>
I'm trying to build a view using SQL Server, which is based on the following table of information from a web site access log.<br>
<br>
user page access-time<br>
=======================================<br>
tom main.html 12:37:05<br>
tom forum.html 12:40:44<br>
tom careers.html 12:41:50<br>
harry main.html 12:40:48<br>
harry news.html 13:00:00<br>
dick news.html 12:39:03<br>
<br>
From this, I'm trying to derive a view for the length of the session:<br>
<br>
user start time end time<br>
======================================<br>
<br>
tom 12:37:05 12:41:50<br>
dick 12:39:03 12:39:03<br>
harry 12:40:48 13:00:00<br>
<br>
The trick seems to be deriving the end time for a session, which occurs when:<br>
<br>
A new user occurs in the log table (they are all sorted by user name)<br>
<br>
OR<br>
<br>
The user currently being processed has a log entry time that occurs more than 30 minutes after the previous entry (i.e., the user has been inactive for more than 30 minutes)<br>
<br>
Is this slightly beyond SQL (I have a feeling that a view like this would require conditional statements to build it)?<br>
If it is beyong SQL, are there other ways to build views in SQL Server that might solve the problem?<br>
<br>
Thanks in advance .....<br>
<br>
<br>
Raze<br>
<br>
<br>
<br>
<br>
<br>
<br>

 

[yaffle]

I don't know about the syntax in SQL Server, but in Oracle the following might do the job:<br>
<br>
create view user_logons_view as<br>
select v.user_name, v.start_time, min(v.end_time)<br>
from (select a.user_name, b.access_time start_time, a.access_time end_time<br>
from access_times a, access_times b<br>
where not exists<br>
(select null<br>
from access_times a0<br>
where a0.access_time between a.access_time<br>
and a.access_time+(1/48)<br>
and a0.user_name = a.user_name)<br>
and not exists<br>
(select null<br>
from access_times b0<br>
where b.access_time between b.access_time-(1/48)<br>
and b.access_time<br>
and b.user_name = b.user_name)<br>
and a.user_name = b.user_name<br>
and a.access_time &gt;= b.access_time) v<br>
group by v.user_name, v.start_time;<br>
<br>
N.B. this won't help you with logins from multiple PCs or in detecting sessions truncated by loss of connection or closure of the browser. It's probably woefully inefficient too ;-) If there's such a thing as a unique session identifier knocking about during your connections, I'd suggest you try logging that as well; it'd make this sort of thing a fair bit easier.

 

[raze]

Whew!<br>
<br>
Thanks.<br>
<br>
I'll give it a try ..... <br>
<br>
Though I think it might be better to do the hard stuff as a program on the middle-tier ....<br>

 

[calahans]

why not try:<br>
<br>
select a.user,a.login,b.logout from<br>
(select user,min(accesstime) as login<br>
from access_times group by user) a,<br>
(select user,max(accesstime) as logout<br>
from access_times group by user)b<br>
where a.user=b.user<br>
<br>
I have not tried this - don't have the correct software on this machine.<br>
<br>
C

 

[raze]

Thanks. I'll fiddle with the syntax and give it a bash.<br>
<br>
... <br>