Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004

    Unanswered: query to return values for closest date

    I'm trying to make a query based on 2 different tables. I want to see all the values from one table based on a date field in the other one. So I want from table1--[labdate], [labresult]-----but i want the labdate/results to be the closest one to the [enrollmentdate] from table 2.

    I already have a query with the between function to specify the date range i want---so it gives me the labs that were taken 3 months prior to the enrollmentdate or 2 weeks after. Now i want only 1 lab result for each person--the closest to their enrollment date. Any ideas?

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    a possible outline approach (totally untested).

    you have TargetDate and AnotherDate... either date could be the earlier... you want to find the AnotherDate in your table that is "closest" to TargetDate.

    so it's a question of:
    1 finding the difference
    2 removing the sign
    3 finding the smallest of these unsigned differences

    anyDouble = iif(TargetDate < AnotherDate, AnotherDate - TargetDate, TargetDate - AnotherDate)
    provides an unsigned days-point-fractionaldays date difference.

    SELECT TOP 1 anyDouble FROM ...... ORDER BY anyDouble
    finds the smallest.

    ? izy
    currently using SS 2008R2

  3. #3
    Join Date
    May 2004
    1. Create a query/table that sums up all the differences between the dates and make sure you multiply by negative 1 with values less than 0
    dlf - date looking for
    dw - date you know you want
    SELECT dlf,IIF(dw-dlf < 0, -1 * (dw-dlf), dw-dlf) as difference

    Call this q_difference
    2. SELECT MIN(difference) smallest_diff from q_difference

    call this q_smallest

    3. SELECT dlf FROM q_difference diff, q_smallest sml WHERE sml.smallest_diff = dif.difference

    And at that point you have your smallest date difference...
    There's prolly an easier way...


  4. #4
    Join Date
    Jun 2004

    Thanks but doesn't work

    I tried both suggestions....the first one seemed like it could work but i don't know where to go with it. It shows me the difference but the SELECT TOP # is where it seems to go wrong. It only returns results for labs taken on the same day as the enrollment date (so difference=0).

    I think the second suggestion is missing some parts....if i knew more about sql i might be able to fill in the blanks but i don't.

    Any other ideas?

Posting Permissions

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