473,289 Members | 1,848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Closest Match

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
1 3968
MMcCarthy
14,534 Expert Mod 8TB
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;
  5.  
Nov 8 '07 #2

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

Similar topics

13
by: mike | last post by:
I have ListArray with number in Eg: 1, 1.456, 2.43, 4, 6.78 next i have a decimal variable containing one number EG: 1.786 Could someone please tell me how i find the "closest match" number...
2
by: Joe | last post by:
Hello all! I need to display a list of names similar to a spell checker where the list is the closest match to the name entered. Not too sure where to begin with this - any suggestions? Thanks,...
2
by: Daniel Di Vita | last post by:
I have created an ASP.NET page that allows the user to page through a result set. I need to expand on this. On that same page I a filed where the user can type in a search string. When they click...
2
by: Daniel Di Vita | last post by:
I have created an ASP.NET using page that allows the user to page through a result set. I need to expand on this. On that same page I have a field where the user can type in a search string. When...
0
by: muggy440 | last post by:
Hello; The code below is one of solving the closest pair problem but some parts missing can any one help me out. #include <stdio.h> #include <stdlib.h> #include <math.h> #include <time.h>
2
by: sturgeob | last post by:
Yep. Newby. I am asked to find the closest value to user defined value if not right on the mark. I can get it to determin it I have selected one of the generated numbers, but am at a loss how to...
5
by: jm.suresh | last post by:
Hi I have three objects, all of them are instances of classes derived from a base class. Now, given one of the instance, I want to find the closest relative of the other two. How can I do this? ...
22
by: Steve Richter | last post by:
Does the .NET framework provide a class which will find the item in the collection with a key which is closest ( greater than or equal, less than or equal ) to the keys of the collection? ex:...
5
by: p309444 | last post by:
Hi. I have an application in which the user can select a location and view it's distance from several Points Of Interests (POIs). When I retrieve these distances, I would also like to retrieve...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.