tracking IP addresses

Question

I want to track IP addresses visited my website. I want to know what time and what page they visit. I store ip address in VISITORIP, date entered in DATEENTERED and page URL in HTTPADDRESS columns.

I want to group them by dates. My outcome should be like:

TIME                    PAGE
 7/12/2009
3:16:27 PM  ?Section=products&SubSection=products&CATEGORYID=1
2:46:50 PM  ?Section=products&SubSection=products&CATEGORYID=8
2:20:45 PM  ?Section=products&SubSection=products&CATEGORYID=11
 7/11/2009
9:34:28 AM  ?Section=products&SubSection=products&CATEGORYID=7
9:33:31 AM  ?Section=products&SubSection=products&CATEGORYID=2
 7/10/2009
9:53:16 PM  ?Section=products&SubSection=products&CATEGORYID=9
9:34:14 PM  ?Section=products&SubSection=products&CATEGORYID=4
9:27:11 PM  ?Section=products&SubSection=products&CATEGORYID=3
9:15:47 PM  ?Section=products&SubSection=products&CATEGORYID=15

Problem is, I could not group them by dates. I get the outcome as:

07/12/2009
7/12/2009 3:16:27 PM    /html/default.aspProcess=HomeNewSeason&IMAGECONTENT=bg_home_newtaste.gif
7/12/2009 3:16:27 PM    /html/default.aspProcess=HomeBestSeller&IMAGECONTENT=bg_home_customerschoice.gif
07/12/2009
7/12/2009 3:16:27 PM    /html/default.aspProcess=HomeNewSeason&IMAGECONTENT=bg_home_newtaste.gif
7/12/2009 3:16:27 PM    /html/default.aspProcess=HomeBestSeller&IMAGECONTENT=bg_home_customerschoice.gif
07/09/2009
7/9/2009 5:37:02 PM     /html/default.aspSection=checkout
07/09/2009
7/9/2009 5:37:02 PM     /html/default.aspSection=checkout
07/09/2009
7/9/2009 5:37:02 PM     /html/default.aspSection=checkout

My codes:

<%
     Case "TrackIP"

     IPADDRESS = Request.QueryString("IPADDRESS") 

    SQL = "SELECT CONVERT(VARCHAR(10), DATEENTERED, 101) AS DATEENTERED"
    SQL = SQL & " FROM STATS"
    SQL = SQL & " WHERE VISITORIP = '"& IPADDRESS &"'"
    SQL = SQL & " GROUP BY DATEENTERED" 
    SQL = SQL & " ORDER BY DATEENTERED DESC"
    Set objDateEntered = objConn.Execute(SQL)  
%>
    <table>
        <thead>
            <tr>
             <th>Time</th>
             <th>Page</th>
            </tr>
        </thead>
        <tbody>
<% Do While Not objDateEntered.EOF %>
            <tr class="row-title">
                <td colspan="2"><%=objDateEntered("DATEENTERED")%></td>
            </tr>
<%
    SQL = "WITH LASTVISIT AS ("
    SQL = SQL & " SELECT VISITORIP, MAX(DATEENTERED) AS DATEENTERED"
    SQL = SQL & " FROM STATS"
    SQL = SQL & " WHERE VISITORIP = '"& IPADDRESS &"' AND CONVERT(VARCHAR(10), DATEENTERED, 101) = CONVERT(VARCHAR(10), '"& objDateEntered("DATEENTERED") &"', 101)"
    SQL = SQL & " GROUP BY VISITORIP"
    SQL = SQL & " )"
    SQL = SQL & " SELECT STATS.VISITORIP, STATS.HTTPADDRESS, STATS.DATEENTERED"
    SQL = SQL & " FROM STATS"
    SQL = SQL & " INNER JOIN LASTVISIT"
    SQL = SQL & " ON LASTVISIT.VISITORIP = STATS.VISITORIP AND LASTVISIT.DATEENTERED = STATS.DATEENTERED"
    SQL = SQL & " ORDER BY STATS.DATEENTERED DESC"
    Set objTrackIP = objConn.Execute(SQL)  
%>
<% Do While Not objTrackIP.EOF %>
            <tr class="row-a">
             <td><%=objTrackIP("DATEENTERED")%></td>
             <td class=""><%=objTrackIP("HTTPADDRESS")%></td>
            </tr>
<%
    objTrackIP.MoveNext
    Loop
%>
<%
    objDateEntered.MoveNext
    Loop
%>
        </tbody>
    </table>

edit: I think problem is with my outer query. But I grouped them by DATEENTERED field, I dont know what else can I do.

Answer 1

The problem is in next code line:

SELECT CONVERT(VARCHAR(10), DATEENTERED, 101) AS DATEENTERED

and then you group by this date

GROUP BY DATEENTERED
ORDER BY DATEENTERED DESC

You group and order by the varchar. As I think, you need to use another alias to date when you convert it ro varchar, but you really need to order by dateentered that is datetime type.

Answer 2

It you're trying to get some visitor tracking happening have you thought of using something like Google Analytics?

Answer 3

First query should be

"SELECT 
    DISTINCT CONVERT(VARCHAR(10), DATEENTERED, 101) AS vDATE 
 FROM 
    STATS 
 WHERE 
    VISITORIP = '"& IPADDRESS &"'" 
 ORDER BY 
    DATEENTERED DESC"

then print the vData column

and the 2nd SQL

SELECT 
   VISITORIP, HTTPADDRESS, CONVERT(VARCHAR(8), DATEENTERED, 8) AS vTIME
FROM 
   STATS
WHERE
   CONVERT(VARCHAR(10), DATEENTERED, 101) = '" & objDateEntered("vDATE") &"'
   AND VISITORIP = '"& IPADDRESS &"'
ORDER BY VISITORIP ASC, DATEENTERED DESC

I think this will do the trick..