Optimal search for the nearest date

[sk]

I have the following table

CREATE TABLE Readings
(
ReadingTime DATETIME NOT NULL DEFAULT(GETDATE()) PRIMARY KEY,
Reading int NOT NULL
)

INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050101', 1)
INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050201', 12)
INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050301', 15)
INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050401', 31)
INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050801', 51)
INSERT INTO Readings (ReadingTime, Reading) VALUES ('20051101', 106)
GO

-- list the table
SELECT ReadingTime, Reading FROM Readings
GO
It is a table of readings of a free-running counter that is
time-stamped. I need to determine the value of the reading that
corresponds to the closest date to the supplied date

Are there more optimal/efficient ways of accomplishing this than the
following?

DECLARE @when DATETIME
SET @when = '20050505'

SELECT TOP 1 ReadingTime, Reading FROM Readings
ORDER BY abs(DATEDIFF(minute, ReadingTime, @when))

The above gives me the desired result of ('20050401', 31).
Any suggestions would be appreciated

[Erland Sommarskog]

sk (sh*************@hotmail.com) writes:

It is a table of readings of a free-running counter that is
time-stamped. I need to determine the value of the reading that
corresponds to the closest date to the supplied date

Are there more optimal/efficient ways of accomplishing this than the
following?

DECLARE @when DATETIME
SET @when = '20050505'

SELECT TOP 1 ReadingTime, Reading FROM Readings
ORDER BY abs(DATEDIFF(minute, ReadingTime, @when))

The above gives me the desired result of ('20050401', 31).

This might be better:

SELECT R.ReadingTime, R.Reading
FROM Readings R
JOIN (SELECT TOP 1 ReadingTime
FROM (SELECT ReadingTime = MAX(ReadingTime)
FROM Readings
WHERE ReadingTime < @when
UNION
SELECT ReadingTime = MIN(ReadingTime)
FROM Readings
WHERE ReadingTime > @when) AS a
ORDER BY abs(DATEDIFF(minute, ReadingTime, @when))) AS R2
ON R.ReadingTime = R2.ReadingTime

While more complex, it may perform better, but you will have to
benchmark.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx