Query records where date/time field value is close or equal

[Bdowns]

Table A has a Date/Time field and a field with a bunch of measurements. Table B has a Date/Time field and bunch of other miscellaneous fields. I want to create a query that includes the measurement values from Table A where the Date/Time field is closest to that of Table B, but not greater. Also, sometimes the values are equal and I want those measurement values too. Any ideas?

[rpeare]

how close is 'close' and do you always only want the most recent record ONLY or do you want all the records within, say, 5 minutes, of a specified date/time?

Is your query intended to return only one value?

For instance if I put in 1/1/2013 12:00:00 PM (noon)

and I have records at:

1/1/2013 12:00:00 PM
1/1/2013 11:59:59 AM
1/1/2013 11:59:29 AM


Do you *always* only want to return the top value or do you want to show all records in the, say, 30 seconds, prior to noon which would include 2 of these records.

[Bdowns]

Thanks, those are important questions. For now I am only trying to return one value (the most recent one), but I may benefit from being able to return one value as an average of a few recent records or all falling after the previous record in the table. It would be great to know how to return both an average of recent records in a fixed time interval as well as an average of all the records after the previous record's Date/Time. Any help is greatly appreciated. Thanks!

[rpeare]

Assuming your records can never have the same time stamp returning the record that has the most recent record is pretty easy. Let's say you have a table (tblTest) with two fields (CUSTOMERID and DATETIMESTAMP)

In order to return the most recent record what you'd do is

SELECT TOP 1 * FROM tblTest WHERE CUSTOMERID = x AND DATETIMESTAMP <= y ORDER BY DATETIMESTAMP DESC

Where x would be the PK you're searching for
and y would be the date/time stamp you're trying to get close to

[June7]

Here is more about the TOP N parameter http://allenbrowne.com/subquery-01.html#TopN