Posted by sk on 11/15/05 21:24

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

• Next in forum: SQL-Server DBA-Full Time, Permanent, National Institutes of Health
• Prev in forum: get the DB index SQL
• Thread view: Optimal search for the nearest date

[Reply to this message]