Need possible array help

[notaspdotnet]

I have a SQL table called "EXAMPLE" with 2 columns; personId and reportDate. personId consists of a 36 character GUID. reportDate has a date such as 1/1/2005, 2/1/2005,3/1/2005, etc. The date is always the first of the month. Sometimes they are skipping months, ie; 6/1/2006,7/1/2006,11/1/2006,12/1/2006, etc.

Now to my long winded question:

I have an ASP page that displays the selected month and associated personId data based on passing the personId and reportDate in the querystring. If I am on a page looking at personId '123' and reportDate '2/1/2006', for example, I want to place 2 hyperlinks on the page for the reportDate before and after for that personId. So, using my example, the page should show a link to 1/1/2006 and 3/1/2006. The problem is, if 3/1/2006 is not in the DB, it should go to the next available date in the DB. Also, if 2/1/2006 is the last date, or similarly 1/1/2006 is the first, their should not be a link to the dates before and/or after due to this.

Any ideas?

[jhardman]

The problem is, if 3/1/2006 is not in the DB, it should go to the next available date in the DB.

this one is easy if answered by itself. I assume you are linking with something like:[html]<a href="samePage.asp?date=3/1/2006">next month</a>[/html] right? just open the db with this query:

query = "SELECT * FROM EXAMPLE WHERE reportDate >= " & request("date") & " ORDER BY reportDate"
 

then just use the first record. The big problem is you can't tell with this method if there is anything before the record you pulled up, so it won't work to try this and solve the second question.

Also, if 2/1/2006 is the last date, or similarly 1/1/2006 is the first, their should not be a link to the dates before and/or after due to this.

Any ideas?

This one is tougher. To solve both together you need to do something like this:

query = "SELECT * FROM EXAMPLE ORDER BY reportDate"
objRS.open query, objConn, adOpenDynamic
prevMonth = objRS("reportMonth")
if prevMonth = request("date") then 
   'date requested is first in db, so handle accordingly
   'you might not actually need anything here at all
else
   objRS.moveNext
   do until dateDiff("d", objRS("reportMonth"), request("date")) <=0
      prevMonth = objRS("reportDate")
      objRS.moveNext
   loop
   'the exact date of the previous
   'month is saved as "prevMonth"
   'I would create the link to the previous month here
end if
'you are currently on the record requested, the link to the previous
'month was create if needed. I would try "objRS.moveNext" to find
'the exact date of the next record so that I can write the link to
'the exact date needed next, but that's just me
 

Let me know if this helps.

Jared

[notaspdotnet]

Jared,

Thank you very much. You solved the problem for me. This was a mental block that I could not figure out.