asked on 10/9/2011

Find record with nearest Date.

I have a table with transactions which include a date column with a British format date. I want to query this table to return the transaction with the nearest date to my known date where the vehicle is equal to X9ABC and the status is between 1 and 5 and the date is 07/10/2011 14:15:16. The table has 150000 plus records and performance should be considered. I have seen examples using datediff and Top(1) but don’t know how to build the query for my requirements. Anyone help with this.

Table Structure
Vehicle            Date             Status             Driver
X9ABC            07/10/2011 09:31:22 2            Bob
X9ABC            07/10/2011 11:01:32        5            Bob
X8ABC        07/10/2011 18:11:09 6            John

Microsoft SQL Server

Last Comment

JoeBo747

10/9/2011

ASKER CERTIFIED SOLUTION

Eyal

10/9/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Olaf Doschke

10/9/2011

What type is the date column? If it's a datetime field it is totally unimportant what format you see it in, the datetime binary format is far from the human readable format anyway, and that is what is stored and what you work on, not the human readable format.

Bye, Olaf.

Amick

10/9/2011

I don't have a working SQL Server to test the syntax on, but it would be something like:

select top 1 *, abs( DATEDIFF(second, '07/10/2011 14:15:16',tablename.Date))  as DD 
from table 
where vehicle="X9ABC" and
status  between 1 and 5
order by DD

Open in new window

JoeBo747

10/9/2011

ASKER

Hi Eyal

I have tried your suggestion but it does not return a record.

SELECT dwJobAuditId, Vehicle
FROM JobAudit
WHERE (dwJobAuditId IN
(SELECT TOP (1) dwJobAuditId
FROM JobAudit AS JobAudit_1
ORDER BY ABS(DATEDIFF(s, dtDate, CONVERT(datetime, '07/10/2011 14:15:16', 103))))) AND (Vehicle = '[X9ABC]')

Eyal

10/9/2011

insure that the inner query returns record

JoeBo747

10/9/2011

ASKER

Hi Eyal

That’s where the issue was, if I write the query as:
SELECT TOP (1) dwJobAuditId, Registration, dtDate
FROM JobAudit AS JobAudit_1
WHERE (Vehicle= ' X9ABC')
ORDER BY ABS(DATEDIFF(s, dtDate, CONVERT(datetime, '07/10/2011 14:15:16', 103)))

The query then returns the record I expect without resorting to the subquery. Would you use this method ?

Eyal

10/9/2011

it's even better then my version :) use that

JoeBo747

10/9/2011

ASKER

Thanks for both of your comments. But you provided the solution so the points are yours.

Thanks