Displaying recordset with items grouped by year/month?

[Ken Fine]

Using ASP, I want to display a series of records grouped by nested years and
months, e.g.:

2004
December
Record1
Record2
Record3
November
Record4
Record5
...
...
2003

This would be based off of a date value stored with each item in the
recordset.

Does someone have a snip of nested looping script to break this down by
date in this manner?

Extra credit: Ultimately what I want to do with this is create a folding
"tree" which which people can expand and contract the year headers. This
would save some beating on the database server. The following script could
be adapted to add persistence:
http://www.dynamicdrive.com/dynamicindex1/navigate1.htm

If someone can at least help me build the uncollapsed tree of records, that
will get me started.

Thanks,
-KF

 

[Evertjan.]

Ken Fine wrote on 04 apr 2004 in
microsoft.public.inetserver.asp.general:

Using ASP, I want to display a series of records grouped by nested
years and months, e.g.:

2004
December
Record1
Record2
Record3
November
Record4
Record5
...
...
2003

This would be based off of a date value stored with each item in the
recordset.

Pseudocode:

year = -1
month = -1

get firstrecord

do until no-more-records
if recordyear>year then
year=recordyear
month=-1
write recordyear cr
end if
if recordmonth>month then
month=recordmonth
write tab monthtext(recordmonth) cr
end if
write tab tab record cr
get nextrecord
loop

 

[Ken Fine]

Thank you, Evertjan. This was very helpful. On the basis of your suggestion,
I hacked up some code. The only thing I'd really like now is if there was a
way it could display items as a reverse chronological listing, rather than
from the oldest items to the newest. Can someone suggest how I would rework
the following code to make it function with a SQL query that returned the
items in descending chronological order?

<%
theyear =-1
themonth = -1

While ((Repeat2__numRows <> 0) AND (NOT rsArticlesByDate.EOF))

if year(rsArticlesByDate.Fields.Item("Con_PubDate").Value) >theyear then
theyear=year(rsArticlesByDate.Fields.Item("Con_PubDate").Value)
themonth=-1
response.write "<tr><td><br><span
class=""largeHeadlineArialGray"">" &
year(rsArticlesByDate.Fields.Item("Con_PubDate").Value) &"</span>
</td></tr>"
end if
if month(rsArticlesByDate.Fields.Item("Con_PubDate").Value)>themonth
then
themonth=month(rsArticlesByDate.Fields.Item("Con_PubDate").Value)
response.write "<tr><td style=""padding-left:15px""> "& "<span
class=""subhead""><b> " &
monthname(month(rsArticlesByDate.Fields.Item("Con_PubDate").Value)) &
"&nbsp;"& theyear & "</b></span></td></tr>"
end if
response.write "<tr><td style=""padding-left:35px""><span
class=""subhead"">"&
Trim(FilterHTML(rsArticlesByDate.Fields.Item("Con_Title").Value)) &
"</span><br></td></tr>"

Repeat2__index=Repeat2__index+1
Repeat2__numRows=Repeat2__numRows-1
rsArticlesByDate.MoveNext()
Wend
%>

 

[Evertjan.]

Ken Fine wrote on 05 apr 2004 in
microsoft.public.inetserver.asp.general:

Pseudocode:

year = -1
month = -1

get firstrecord

do until no-more-records
if recordyear>year then
year=recordyear
month=-1
write recordyear cr
end if
if recordmonth>month then
month=recordmonth
write tab monthtext(recordmonth) cr
end if
write tab tab record cr
get nextrecord
loop

[..............]
Thank you, Evertjan. This was very helpful. On the basis of your
suggestion, I hacked up some code. The only thing I'd really like now
is if there was a way it could display items as a reverse
chronological listing, rather than from the oldest items to the
newest. Can someone suggest how I would rework the following code to
make it function with a SQL query that returned the items in
descending chronological order?

[please do not toppost on usenet]


SELECT ... OREDER BY recordDate DESC


Pseudocode:

year = 9999
month = 99

get firstrecord

do until no-more-records
if recordyear<year then
year=recordyear
month=99
write recordyear cr
end if
if recordmonth<month then
month=recordmonth
write tab monthtext(recordmonth) cr
end if
write tab tab record cr
get nextrecord
loop

 

[Evertjan.]

Evertjan. wrote on 05 apr 2004 in microsoft.public.inetserver.asp.general:

SELECT ... OREDER BY recordDate DESC

Order !, Order ! Order !