SEO

#1 (**permalink**) 02-29-2008, 01:55 PM

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

#2 (**permalink**) 02-29-2008, 01:56 PM

sk (shripathikamath@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, esquel@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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Optimal search for the nearest date