Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Aug 2007
    Posts
    93
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Grouping a recordset by month

    Hi, i'm trying to return a list of hyperlinked news items on my site. Everything works fine, apart from the fact I want to split the items by month, printing the month name then displaying the records belonging to that month. I think i've had a mental block here as i'm sure it shouldn't be too difficult. The only way i can see to do it is to create another query within the main while loop, which takes the current month and tries to query the database for all the records in that month. This would involve checking the current month (created using the mid function) against the date column in the database, which would then involve using an SQL substring function as the date is in dd/mm/yyyy format......am I over-complicating a really simple problem here or is this the only way to do it?

    Grateful for any help!

    this is the current code (without the nested loop)
    Code:
    'Get list of stories, hyperlink to each item
    rsMenu.Open "select * from latest_news where status is null order by id desc", dbConn, 1 
    Response.Write("<ul>")
    
    dim the_date, the_year, the_month, month_text
    
    while not rsMenu.eof
    'organise by date
    the_date = rsMenu("date")
    the_year = mid(7,4)
    the_month = mid(the_date, 4,2)
    
     Select Case the_month
      case "01"
       month_text = "January"
      case "02"
       month_text = "February"
      case "03"
       month_text = "March"  
      case "04"
       month_text = "April"
      case "05"
       month_text = "May"
      case "06"
       month_text = "June"
      case "07"
       month_text = "July"
      case "08"
       month_text = "August"
      case "09"
       month_text = "September"
      case "10"
       month_text = "October"
      case "11"
       month_text = "November"
      case "12"
       month_text = "December"
      end select
    
    'Print out the month, then loop through the stories in that month
    Response.Write("<h3>" & month_text & "</h3><br/>")
    
    		Response.Write("<li><a href = 'news.asp?id=" & rsMenu("id") & "'>" & rsMenu("title") & "</a></li>")
    	
    rsMenu.movenext
    wend
    
    Response.Write("</ul>")

  • #2
    Senior Coder shyam's Avatar
    Join Date
    Jul 2005
    Posts
    1,563
    Thanks
    2
    Thanked 163 Times in 160 Posts
    why not let sql do the heavy lifting?
    Code:
    'Get list of stories, hyperlink to each item
    rsMenu.Open "select * from latest_news where status is null order by datepart(month, date), id desc", dbConn, 1
    You never have to change anything you got up in the middle of the night to write. -- Saul Bellow

  • Users who have thanked shyam for this post:

    nicky77 (11-16-2007)

  • #3
    New Coder
    Join Date
    Aug 2007
    Posts
    93
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Thanks shyam - i wasn't aware of the datepart function, very handy indeed!


  •  

    Posting Permissions

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