Complex Query Question - Parsing web logs

[kono316]

This is a tough one, I've got a bunch of data parsed out from a web log into
an Access table. There are 3 steps to a user's session and I'm trying to
subtract a user's step 3 timestamp from the step 1 so that I can find out
their total wait time. The part that I can't get working is when a user has
multiple sessions, unfortunatly I don't have a session ID that will seperate
the records. So when I run a max timestamp - min timestamp I'm not getting
the accurate picture if a person had a session at 9 a.m. and one at 10 a.m.

The data I'm working with has an indicator for each step, time for each
step, and a user ID for each step.

Thanks!

 

[KARL DEWEY]

What about --
first one - min step 1 to min step 3
last one - max step 1 to max step 3

Does your timestamp have date also? Use Ranking In A Group query.
SELECT Q.[Group], Q.[Item_no], Q.[Points], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.[Group], Q.[Points] DESC;

Group on date and rank on time.
First session would be rank 1 to rank 3.
Second session would be rank 4 to rank 6.
Etc.

 

[ntc]

web log analysis is a whole industry; have you looked at sawmill or
webtrends...there is a bunch of products out there....