By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,558 Members | 1,607 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,558 IT Pros & Developers. It's quick & easy.

Optimal search for the nearest date

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.