Highest # page Views in One Day:

# Thread: Highest # page Views in One Day:

1. Junior Member
Join Date
Dec 1969
Posts
18

## Highest # page Views in One Day:

Can someone help me with how to pull this prom a database?<BR>the Fields on the website is id,Date,Doc.<BR>I suppose my question is how do i loop and count the dates that are the same and come out with an int at the end, i&#039;m completly stuck on this one have been for a few hours now :( can anyone help? Thank you.<BR>Mike

2. Senior Member
Join Date
Dec 1969
Posts
1,843

## RE: Highest # page Views in One Day:

If someone can figure out how to do this with a SQL Query then that would be great. <BR><BR>But.. This will work to. <BR><BR>SQL="Select Date From YourTable Order By Date"<BR>Set RS = ConnObj.Execute(SQL)<BR>arrDates = RS.GetRows()<BR><BR>For i = 0 to ubound(arrDates,2)<BR> If arrDates(0,i) = ThisDate Then<BR> TempCount = TempCount + 1<BR> Else<BR> If TempCount &#062; HighCount Then<BR> HighCount = TempCount<BR> HighDate = arrDates(0,i-1)<BR> End If<BR><BR> TempCount = 1<BR> ThisDate = arrDates(0,i)<BR> End If<BR>Next<BR><BR>Response.Write("The Busiest Day was: " & HighDate & " With " & HighCount & " Page Views!")<BR><BR><BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
1,843

## A Bit Better:

For i = 0 to ubound(arrDates,2)<BR> If arrDates(0,i) = arrDates(0,i-1) Then<BR> TempCount = TempCount + 1<BR> Else<BR> If TempCount &#062; HighCount Then<BR> HighCount = TempCount<BR> HighDate = arrDates(0,i-1)<BR> End If<BR><BR> TempCount = 1<BR> End If<BR>Next<BR><BR>It gets rid of the ThisDate variable.. not needed.. i just wasn&#039;t thinking

4. Senior Member
Join Date
Dec 1969
Posts
1,843

## Sh*t

Ignore this post, use the first code I posted. <BR><BR>If you run this then the arrDates(0,i-1) field will cause a "sub script out of range" error. You could do a logic check to see if it&#039;s already at the first line of the array, but hell with it. Use the first code. I get kinda lazy. <BR><BR>Or, and here&#039;s a novel idea, since I just told you what the problem was, fix it yourself and use the more efficient code above.

5. Junior Member
Join Date
Dec 1969
Posts
18

## RE: Highest # page Views in One Day:

Cool -- almost there How do i get this to work when the date field contains "11/13/2002 2:03:03 AM" (it looks for the time too)<BR>Thanks in advance

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## DO NOT LOOP!

That&#039;s the WHOLE POINT of using a database, so you can use *SQL*!!!<BR><BR>&#060;%<BR>SQL = "SELECT TOP 1 [Date], Count(*) AS theCount FROM yourtable GROUP BY [Date] ORDER BY theCount"<BR>Set RS = yourConnection.Execute( SQL )<BR>theDate = RS("Date")<BR>theCount = RS("theCount")<BR>RS.Close<BR>Response.Write "The highest count was " & theCount & " on " & theDate<BR>%&#062;<BR><BR>If you are using ACCESS, then the tail end of that query has to be<BR> ORDER BY 2<BR>instead of<BR> ORDER BY theCount<BR><BR>And you need the [...] around Date if this is an Access DB, as Date is an Access keyword.<BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## Trivial if you use SQL...

...just use <BR> SELECT TOP 1 DateValue([Date]) as theDate, COUNT(*) as TheCount<BR> FROM table<BR> GROUP BY DateValue([Date])<BR> ORDER BY 2<BR><BR>if you are using Access.<BR><BR>Ask for the answer if you are using SQL Server.<BR><BR>

8. Senior Member
Join Date
Dec 1969
Posts
1,843

## Yes, better, of course

And to answer the question that was posed to my answer above, you will need to do:<BR><BR> Group By FormatDateTime([Date],VbShortDate)<BR><BR>This will work in access.. i&#039;m almost positive.. and it will be needed so you only group by day.

9. Senior Member
Join Date
Dec 1969
Posts
1,843

## Don't listen to me, Listen to Bill

My answer will work, bills is better. DateValue([Date]) makes more sense the FormatDateTime. <BR><BR>I&#039;m going to bed now. I&#039;ll be back when bill&#039;s gone :P

10. Junior Member
Join Date
Dec 1969
Posts
18

## RE: Trivial if you use SQL...

Hi Bill yes i&#039;m using SQL - i&#039;m using access ,<BR>error<BR>ADODB.Recordset error &#039;800a0cc1&#039; <BR><BR>Item cannot be found in the collection corresponding to the requested name or ordinal. <BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•