I am having a problem. See, I have a small table that tracks users visits to my site. And I want to have it show all the people visiting first on the bottom of the page, people just visiting on top. I can solve that with a simple ORDER BY DateVisited clause. My problem is that I also want to group peoples IP addresses together too. If I don't, when two people visit the website around the same time, browse around, I get overlapping of IPs.
Example how it is now:
1. 127.0.0.1 /default.asp 12:15PM
2. 215.394.293.494 /default.asp 12:16PM
3. 127.0.0.1 /contact/default.asp 12:17PM
Example of how I would like it:
1. 215.394.293.494 /default.asp 12:16PM
2. 127.0.0.1 /default.asp 12:15PM
3. 127.0.0.1 /contact/default.asp 12:17PM
I can ORDER BY IpAddress first, but then my dates are wacked out and the people with lower initial IP numbers appear first, which is meaningless. Any advise?Could you add these values to your sample data and show us everything sorted the way you want to see it?
127.0.0.1 /default.asp 11:00AM the next day
127.0.0.1 /default.asp 10:00AM the same day
125.5.5.5 /default.asp 11:00AM the same day
125.5.5.5 /default.asp 11:00PM the same day
215.394.293.494 /default.asp 11:00AM the next day
Terri|||Here is my current SQL statement, notice how I only show the results for 1 day at a time. That way, I just page back and forth through the days...
"SELECT * FROM PageViews WHERE (CONVERT(datetime, CONVERT(varchar, LastVisited, 101)) = '" & dDate & "')" AND Remote_Addr <> '" & User & "' ORDER BY LastVisited,Remote_Addr;"
Variables:
dDate = Date selected
User = IP Address
Does this help? What do you want me to do next? You've helped me before Terri so I know you have a good answer for me. :)|||OK, you are only dealing with one date at a time. That helps.
Where would these fit in -- how would your 6 returned rows be sorted?
127.0.0.1 /contact/default.asp 9:00AM
127.0.0.1 /contact/default.asp 9:00PM
127.0.0.1 /default.asp 9:15PM
Terri
PS - glad I've been able to help in the past :-)|||It would return the results like so...
127.0.0.1 /default.asp 9:15PM
127.0.0.1 /contact/default.asp 9:00PM
127.0.0.1 /contact/default.asp 9:00AM
which is not the wrong thing... the problem is when two IPs visit the website at the same time... then I have something crazy like this...
127.0.0.1 /default.asp 9:15PM
123.456.0.1 /default.asp 5:05PM
127.0.0.1 /contact/default.asp 9:00PM
127.0.0.1 /contact/default.asp 9:00AM
I would prefer a look like this:
127.0.0.1 /default.asp 9:15PM
127.0.0.1 /contact/default.asp 9:00PM
127.0.0.1 /contact/default.asp 9:00AM
123.456.0.1 /default.asp 5:05PM
The 124.456.0.1 is listed last only because 127.0.0.1 visited a page last at 9:15PM. Had 127.0.0.1 not visited the two pages at 9:15PM and 9:00PM in the evening, he/she would actually be listed under 123.456.0.1 like so:
123.456.0.1 /default.asp 5:05PM
127.0.0.1 /contact/default.asp 9:00AM
Does this make any sense or am I just crazy?
Thanks,
B|||Anybody have any ideas? I am really looking forward to making this work!|||I am sorry, I lost site of this.
The problem is I am now completely confused on what you are looking for, because your latest examples seem to be in reverse chronological order, which they weren't at first.
Maybe someone else can make sense of it, or maybe you can make it clearer.
Terri|||I'd want to request for a clearer explaination of what you're trying to do. I'm a bit medicine head today, but reading this thread made me dizzy. Looking at your convert in the sql alone made me cringe.
Be a little more clear as to what you need, and let's hammer this one out.|||I'm sorry that everything seems to be confusing. I will try to explain everything over, and if I still don't make sense, it's probably just me and this crazy thing I'm trying to do. But thanks for looking...
Okay, so I record stats of people visiting my site. When somebody visits, it will record an entry and display it like so:
samplecableuser.comcast.net - 68.12.94.11 - /default.asp - 1 visit - 12:22pm
I turn around and format it in HTML to look similar to this:
samplecableuser.comcast.net (68.12.94.11)
/portfolio.asp (1 visit) at 5:26pm
/contactus.asp (1 visit) at 2:24pm
/default.asp (1 visit) at 11:22am
(notice how the first page that the person visited is towards the bottom, so as the person surfs on the site more, it will list the last page that person visited on top)
Now, this is perfect, and if I sort the table by the LastVisited field, all is well. That is, until two people surf the website at the same time, then my page does the following:
samplecableuser.comcast.net (68.12.94.11)
/portfolio.asp (1 visit) at 5:26pm
/contactus.asp (1 visit) at 2:24pm
anotherperson.bellsouth.net (230.128.43.4)
/test.asp (1 visit) at 1:55pm
samplecableuser.comcast.net (68.12.94.11)
/default.asp (1 visit) at 11:22am
Notice how they overlap? I would like to have it sorted by date, LastVisited is the field. But, I would also like to have it group the users together by IP address. so that the following above looks like this:
samplecableuser.comcast.net (68.12.94.11)
/portfolio.asp (1 visit) at 5:26pm
/contactus.asp (1 visit) at 2:24pm
/default.asp (1 visit) at 11:22am
anotherperson.bellsouth.net (230.128.43.4)
/test.asp (1 visit) at 1:55pm
Now, the samplecableuser.comcast.net person should be at the bottom, but since the last visited was at 5:26pm, he is put at the top. Is there some kind of UBound() on dates? Would that solve it? I am not very good with the GROUP BY clause because you can't really use SELECT * FROM whatever.
Any suggestions?
Thanks,
B|||Honestly, I was afraid you'd reply back so quickly. heh
Alright. I get your drift.
I think it's because you have to convert the date to a varchar. I still don't understand why you're doing that. I hope I'm not just stuck on that one.
What I'd say is group by the Remote_Addr, and sort by DateTime Desc
That should be what you want. Once you group it by the Remote_Addr, there can't be dupes since it's grouped by them anyways.
if you need help with the sql, try out www.sqlcourse.com|||Well the only reason I covert the date to varchar is because its the only way I could get the WHERE statement to pull out the records I wanted on SQL Server. If there is a better way, I would like to know. The dates get saved like so MM/DD/YYYY HH:MM:SS PM, but when I do that varchar conversion it becomes just MM/DD/YYYY, which I match with whatever todays date is.|||Another thing I am having a issue with, you say "group by remote_addr", but it wants me to include them all in the grouping or whatever. I get this message:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Column 'PageViews.TrackerID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
And if I include that in the SQL query, it will go to the next column, and so on until I am done with all the columns, then it will display. But, is that right?|||Instead of doing the date comparison that way, use DATEDIFF.
WHERE DATEDIFF(d,LastVisited,'" & dDate & "') = 0 AND..."
Terri|||See. What Terry mentioned is much sexier than casting to varchar.
Also, in regards to having to select everything properly.. I'm wondering if you're doing this inline sql or through stored procedure.
The reason I ask this is because if you're having so much diffculty in creating one sql statement to do everything for you, you may wish to create a procedure to execute through a few different statements to obtain the desired result.|||Got the date thing fixed, but what about the recordset? I can't do stored procedures... I just need it to group by one field only. Like so:
SELECT * FROM Table
WHERE Date = This
GROUP By IP
ORDER BY Date
If that statement could work, it would be right. But, in SQL Server (2000), you can't group items together unless everything in that * is in the group statement too. WTF?
Thanks,
B
No comments:
Post a Comment