# Thread: query to return values for closest date

1. Registered User
Join Date
Jun 2004
Posts
11

## 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. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
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

3. Registered User
Join Date
May 2004
Location
NH
Posts
87
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...

-Warren

4. Registered User
Join Date
Jun 2004
Posts
11

## 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
•