By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,547 Members | 1,126 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,547 IT Pros & Developers. It's quick & easy.

Closest Match

P: 1
I need help writing an SQL statement in Access 2007 to select the closest date/time. I have Spot Time (the date/time commercials ran) and Call Time (the date/time we received calls).

I need to match these two fields so that I can tell which calls came in within 5 minutes before the Spot Time and which came in 15 minutes after.

Between DateAdd("n",-5,[SpotTime]) AND DateAdd("n",15,[SpotTime])

This gives me results within that 20 minute time range, but does not match each call up to the closest time.

For example,

SpotTime--------------- CallTime
6/30/2007 10:45 AM 6/30/2007 10:55 AM
6/30/2007 10:50 AM 6/30/2007 10:55 AM
6/30/2007 10:55 AM 6/30/2007 10:55 AM

In this example I would need the three Call Times to correspond with the 10:55 AM spot because the Call Time occurs very close to the Spot Time. I am unclear how to proceed.

Thanks in advance!
Nov 7 '07 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 10K+
P: 14,534
Try something like this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 3 CallTime, DateDiff("s", SpotTime, CallTime) As Seconds
  2. WHERE [SpotTime]  Between DateAdd("n",-5,[SpotTime]) 
  3. AND DateAdd("n",15,[SpotTime])
  4. ORDER BY Seconds;
Nov 8 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.