Grouping dates by year 1

[pixel69]

I have a set of records for news articles.
Dates are stored dd/mm/yyyy.

How can i group the records by year within the sql query? eg:

<B>News for 2007</B>
1. blah
2. Blah
3. etc etc

<B>News for 2006</B>
1. blah
2. Blah
3. etc etc

THis is the current code that i'm using:

<%sql = "select article_id, article_title, article_text, article_path, article_date, article_source from news_article order by article_date"
Set rs = dbMain.Execute(sql)
%>
<OL>
<%If Not rs.EOF Then
Do While Not rs.EOF
Response.Write("<LI><B>" & rs(1) & "</B>")
Response.Write("<BR />" & rs(2))
Response.Write("<BR />Read this article: <a href='" & rs(3) & "' target=_blank>" & rs(1) & "</a>")
If Trim(rs(4)) <> "" Then Response.Write("<BR />Article date: " & rs(4))
If Trim(rs(5)) <> "" Then Response.Write("<BR />Source: " & rs(5))
Response.Write("<BR /><a href='newsadmin.asp?action=delete&articleid=" & rs(0) & "'>Delete this Article</a><HR color='#bccd86' /></LI>" )
rs.MoveNext
Loop
End If%>
</OL>

 

[Tarwn]

While your looping use a temporary variable to hold on to the last year you processed and compare it to the year for the current record. If they are different output a new header for your new year. Something like:

<UL>
    <%
    [s]If Not rs.EOF Then[/s] 'see comment 1 after code block

    Dim lastYear

    Do While Not rs.EOF
        If lastYear <> Year(trim(rs(4))) Then
            'is this any run after the first?
            If Not IsEmpty(lastYear) Then
                'end open OL and LI
                Response.Write "</OL></LI>"
            End If

            'start a new section
            Response.Write "<LI><B>News For " & Year(rs(4)) & "</B><OL>"
            lastYear = Year(rs(4))
        End If

        Response.Write("<LI><B>" & rs(1) & "</B>")
        Response.Write("<BR />" & rs(2))
        Response.Write("<BR />Read this article: <a href='" & rs(3) & "' target=_blank>" & rs(1) & "</a>")
        If Trim(rs(4)) <> "" Then Response.Write("<BR />Article date: " & rs(4))
        If Trim(rs(5)) <> "" Then Response.Write("<BR />Source: " & rs(5))
        Response.Write("<BR /><a href='newsadmin.asp?action=delete&articleid=" & rs(0) & "'>Delete this Article</a><HR color='#bccd86' /></LI>" )
        rs.MoveNext
    Loop

    'if last year is set, end open OL and LI
    If Not IsEmpty(lastYear) Then Response.Write "</OL></LI>"

    [s]End If[/s]
    %>
</UL>

* comment: You don't need the If rs.EOF statement. If the recordset is EOF it will skip over the loop entirely since it will satisfy the exit condition for the loop on the first try.


Unfortunatly I think the code example I have given above will not work for you. Based on some of the hints in your code it appears that your storing your dates in the database as text, which is a singularly silly thing to do. In addition to that, you somehow have the ability to have a blank string as a date and then is going to throw the logic off a bit. The easiest solution would be to use your SELECT statement to map blank dates to a specific date (like 1/1/1900) and then add one additional if check before outputting your header to see if the year is 1900 and output a different header in that situation. If your using MS Access you can use the IIF statement to output differently, if your using SQL Server it would be the CASE statement, etc.

If your not storing your dates as strings, then you don't need to bother with all the trim statements you have, as there shouldn't be anything to trim off a date (null or otherwise). If you are storing them as text then you have basically rendered those values useless. As you will not be able to accurately order by them, select for date ranges, etc. And the database will not complain when someone comes along and shoves "abcdefg" into your 'date' field, breaking anyhting you have that is looking at that table.

-T

 

[pixel69]

Thanks dude!

That worked perfectly!!