CODEWITHSUNDEEP
sqlsql-server-2005asp-classic
Efe
Efe

Reputation:

listing visits in last 24 hours

I am trying to list last visits per hour in last 24 hours. I thought the following code would do it, but I get the outcome below. Any idea where I screwed it up?

[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0], 

Dim ListHours()
HoursInCurrentDay = "24"
Redim ListHours(HoursInCurrentDay)
For Days = 0 To 23
    ListHours(Hours) = Hours
Next
SQLARR2 = ListHours
For x = 1 to ubound(SQLARR2)
    SQL = "SELECT COUNT(DISTINCT VISITORIP) AS TOTALVISITS"
    SQL = SQL & " FROM STATS"
    SQL = SQL & " WHERE DATEPART(hh, GETDATE()) = '"& SQLARR2(x) &"' AND DATEENTERED BETWEEN CONVERT(VARCHAR(10), GETDATE()-1, 101) AND CONVERT(VARCHAR(10), GETDATE(), 101)"
    Set objVisits = objConn.Execute(SQL)    

    If objVisits.EOF Then
    Else 

    List = List + "[" & SQLARR2(x) & "," & objVisits("TOTALVISITS") & "],"

    End If
Next

Response.Write List

Upvotes: 0

Views: 196

Answers (2)

Ben Griswold
Ben Griswold

Reputation: 18331

Looking at the provided result, I'm noticing that SQLARR2(x) isn't generating a value. All results come up [,0] where the first (missing) element is hour and the second element the count. I don't think ListHours values are being properly defined as you Hours variable doesn't have an assignment (not within the snippet anyhow.)

If you change

For Days = 0 To 23        
   ListHours(Hours) = Hours
Next

to

For Days = 0 To 23        
   ListHours(Days) = Days
Next

the correct hours should be added to your list. This should also fix the query (and the query result) as SQLARR2(x) is used to build the dynamic sql as well.

I hope it helps.

Upvotes: 1

Ben S
Ben S

Reputation: 69342

Couldn't you just use a simple query like:

SELECT * FROM VISITS WHERE (VISIT.TIME > now - 24hours AND VISIT.TIME < now)

Upvotes: 0

Related Questions