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.
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