I record all the web site activities in a SQL table. I embedded an iframe on all the pages accross the site with which I submit the iframe's container's URL into a SQL table along with ip/time/sessionID. So when a user goes from one page to another page within my site, they are tracked.
The table has the following fields; autoID | SessionID | userIP | locationURL | date | Time With this table in place, I have the ability to query which IP is on which page and what the time was at the time of access. This is good enough to implement "How many visitors on the site right now ?" type of functionality. Or even better , you can even tell how many visitors and which URL they are. All one needs then is to build a SQL query giving all the session ID's in that table along with each user's *latest locationURL* within the last X minutes. That X could be passed thru a query string but for the time being say that for one minute... ( 1 minute is practically the same as asking the almost the "right now" ) Following query you'll see is an attempt towards this goal; But I need some help in to do exactly what I have planned to do. I hard coded DATE and TIME values for easier reading. ( In the final version, of course, they will be parameter driven. ) Select sessionID,userIP,locationURL,dateTime from THAT_TABLE WHERE Date = '08/15/2002' AND Time > '13:35:00' Order by Time DESC With the above SQL, you would collect all the records that have been placed after Date = '08/15/2002' AND Time > '13:35:00 PM'. But this is not good enough. Actually, it is more than enough. I would potentially get multiple records for the same user ( sessionID ) if that user happened to browse a few pages after '13:35:00 PM' My challenge is in eliminating the older records for each user. I want to get one record per user and that record should be the latest entry for him. I know I have to deal with the "group by and "having" keywords available in SQL to accomplish this goal. So any help towards this is appreciated. Thank you --- You are currently subscribed to activeserverpages as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED]
