listing visits in last 24 hours.

Question

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

Answer 1

Couldn't you just use a simple query like:

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

Answer 2

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.