stuck building this array

Here is what I need to do, take dates queried from sql and
only display distinct dates plus the number
of times that date value appears. For instance the first
record date is 10/21/2003 and the next record is a later
date, so what would be
displayed is "1 on 10/21/2003", the next row takes the
next date e.g. 10/24/2003, but the following record is
also 10/24/2003, so
I would display "2 on 10/24/2003", and then move past the
duplicate to the next distinct date.
I'm not having much luck creating a solution. I thought
about creating child arrays for each set of dates, but
this list could potentially
be as large as several hundred records or more which would
probably start to affect server memory. Anyone have any
ideas?

Bob
Thu Oct 23 12:20:48 CDT 2003

d wrote:
> Here is what I need to do, take dates queried from sql and
> only display distinct dates plus the number
> of times that date value appears. For instance the first
> record date is 10/21/2003 and the next record is a later
> date, so what would be
> displayed is "1 on 10/21/2003", the next row takes the
> next date e.g. 10/24/2003, but the following record is
> also 10/24/2003, so
> I would display "2 on 10/24/2003", and then move past the
> duplicate to the next distinct date.
> I'm not having much luck creating a solution. I thought
> about creating child arrays for each set of dates, but
> this list could potentially
> be as large as several hundred records or more which would
> probably start to affect server memory. Anyone have any
> ideas?

You need to use a group by query to aggregate your dates. Hopefully you are
storing only the date and not the time:

select datecol, count(*)
from table
group by datecol

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

d
Thu Oct 23 14:01:28 CDT 2003


>
>You need to use a group by query to aggregate your dates.
Hopefully you are
>storing only the date and not the time:
>
>select datecol, count(*)
>from table
>group by datecol
>
>HTH,
>Bob Barrows
>


I actually missed that option, thanks. The time was
included because it's an autofill field on entry but
conversion to varchar fixes it nicely.