Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53

    Question Unanswered: Select closest date to search date

    Hi,

    I'm trying to set up a search function on a page to allow people to find the closest record to a date they search by, rather than just an EOF.

    For example, let's say this is the table

    Code:
    entrydate      entry
    ------------------------
    01/03/03         1
    01/08/03         2
    01/10/03         3
    and they search for 01/05/03. I want record 1 to come up, since it's only 2 days off from when they searched. I tried doing an "ORDER BY DateDiff(dd, entrydate, 01/05/03)" but it just pulled all records starting with the entry in the table. Any ideas on this?

    Thanks!

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Select closest date to search date

    use DateDiff(dd, entrydate, 01/05/03) < 3 in where clause

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use SELECT TOP 1 to retrieve only one record.

    Use
    ORDER BY ABS(DateDiff(dd, entrydate, 01/05/03)) asc
    to get the closest date either before or after your target date.

    How will you handle situations where there is more than one record equally close to your target date?

    blindman

  4. #4
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    Originally posted by blindman
    Use SELECT TOP 1 to retrieve only one record.

    Use
    ORDER BY ABS(DateDiff(dd, entrydate, 01/05/03)) asc
    to get the closest date either before or after your target date.

    How will you handle situations where there is more than one record equally close to your target date?

    blindman

    I have a "Next Entry"/"Previous Entry" navbar at the bottom for multiple entries.

    Unfortunately, this didn't work - it's still pulling the first record in the table and not ordering it by date difference.
    Last edited by Tarkon; 09-22-03 at 15:39.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Post your code, please.

    blindman

  6. #6
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    Originally posted by blindman
    Post your code, please.

    blindman
    Code:
    SELECT * from tblTimeCards WHERE EmployeeID=" & EmployeeID & " ORDER BY ABS(DateDiff(dd, DayOfLeave, " & request.form("finddate") & ")) DESC, PayPeriodID ASC

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This should work:

    SELECT TOP 1 *,
    ABS(DateDiff(dd, DayOfLeave, @finddate))
    from tblTimeCards
    WHERE EmployeeID = @EmployeeID
    ORDER BY ABS(DateDiff(dd, DayOfLeave, @finddate)) DESC,
    PayPeriodID ASC

    Unless request.form("finddate") is returning bogus data, which is why I'd include the ABS function in the result set as well. That way you can see the value you or sorting.

    blindman

  8. #8
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    Originally posted by blindman
    This should work:

    SELECT TOP 1 *,
    ABS(DateDiff(dd, DayOfLeave, @finddate))
    from tblTimeCards
    WHERE EmployeeID = @EmployeeID
    ORDER BY ABS(DateDiff(dd, DayOfLeave, @finddate)) DESC,
    PayPeriodID ASC

    Unless request.form("finddate") is returning bogus data, which is why I'd include the ABS function in the result set as well. That way you can see the value you or sorting.

    blindman
    Nope, still doesn't work - just pulls the very last entry in the table.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry, you should sort in ascending order instead of descending order:

    ORDER BY ABS(DateDiff(dd, DayOfLeave, @finddate)) ASC,
    PayPeriodID ASC

    blindman

  10. #10
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    Originally posted by blindman
    Sorry, you should sort in ascending order instead of descending order:

    ORDER BY ABS(DateDiff(dd, DayOfLeave, @finddate)) ASC,
    PayPeriodID ASC

    blindman
    Now it just pulls the first entry in the table instead of the last.

  11. #11
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    Originally posted by blindman
    Sorry, you should sort in ascending order instead of descending order:

    ORDER BY ABS(DateDiff(dd, DayOfLeave, @finddate)) ASC,
    PayPeriodID ASC

    blindman
    Nevermind, I fixed it. I left off the quotes around the date I was passing to it. It works now.

    Thanks much for the help!

Posting Permissions

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