Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

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


0
JoeBo747
Asked:
JoeBo747
1 Solution
 
EyalCommented:
something like that will do

select * from tblname where id in (select top 1 id from tblname order by abs(datediff(ms, [Date], GETDATE()))
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
 
AmickCommented:
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

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
JoeBo747Author Commented:
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]')
0
 
EyalCommented:
insure that the inner query returns record
0
 
JoeBo747Author Commented:
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 ?
0
 
EyalCommented:
it's even better then my version :) use that
0
 
JoeBo747Author Commented:
Thanks for both of your comments. But you provided the solution so the points are yours.

Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now