Get the closest date

Benny Chow

Hello,

I need help in writing a SQL statement in MS SQL Server 2000 to select
the latest date (i.e., the date closest to or equal to the current date)
for a given date.

For example, in a table I have the following records:
Date Exchange-Rate
01/Sep/03 0.55
05/Sep/03 0.59

If the given date is 02/Sep/03, then the rate 0.55 should be return.
If the given date is 03/Sep/03, then the rate 0.55 should be return.
If the given date is 04/Sep/03, then the rate 0.59 should be return.

Thanks in advanced,

Benny

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 20 '05 #1

Erland Sommarskog

Benny Chow (benny@stg.net.nz) writes:[color=blue]
> I need help in writing a SQL statement in MS SQL Server 2000 to select
> the latest date (i.e., the date closest to or equal to the current date)
> for a given date.
>
> For example, in a table I have the following records:
> Date Exchange-Rate
> 01/Sep/03 0.55
> 05/Sep/03 0.59
>
> If the given date is 02/Sep/03, then the rate 0.55 should be return.
> If the given date is 03/Sep/03, then the rate 0.55 should be return.
> If the given date is 04/Sep/03, then the rate 0.59 should be return.[/color]

Next time, please include CREATE TABLE statements for the tables you
are working with and INSERT statements with sample data. This makes it
possible to post a tested solution.

Thus, this solution is untested:

SELECT exchangerage, date
FROM rates
WHERE date = (SELECT MAX(date)
FROM rates
WHERE date <= @date)



--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #2

David Portas

CREATE TABLE ExchangeRates (rdate DATETIME PRIMARY KEY, exchangerate
DECIMAL(10,2) NOT NULL)
INSERT INTO ExchangeRates VALUES ('20030901',0.55)
INSERT INTO ExchangeRates VALUES ('20030905',0.59)

DECLARE @dt DATETIME
SET @dt = '20030902'

Here's one method:

SELECT exchangerate
FROM ExchangeRates
WHERE rdate =
(SELECT MIN(rdate)
FROM ExchangeRates
WHERE ABS(DATEDIFF(DAY,@dt,rdate))=
(SELECT MIN(ABS(DATEDIFF(DAY,@dt,rdate)))
FROM ExchangeRates))

Or you can use TOP:

SELECT TOP 1 exchangerate
FROM ExchangeRates
ORDER BY ABS(DATEDIFF(DAY,@dt,rdate)), rdate

Personally, I would avoid TOP because it's a MS proprietary extension to
SQL.

--
David Portas
------------
Please reply only to the newsgroup
--

Jul 20 '05 #3

David Portas

Benny wants the closest, before or after the specified date according to his
example.

--
David Portas
------------
Please reply only to the newsgroup
--

Jul 20 '05 #4

Benny Chow

Thanks David, this is exactly what I needed. :)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 20 '05 #5

Steve Kass

Benny,

This might be a little more efficient than other
solutions, but it's not as simple:



select top 1 exchangerate
from (
select exchangerate, pref
from (
select top 1 exchangerate, 1 as pref
from (
select top 3 rdate, exchangerate
from ExchangeRates E1
where E1.rdate >= (
select max(rdate) as lastBefore
from ExchangeRates E2
where E2.rdate < @dt
)
order by rdate
) X
order by case when rdate < @dt then @dt - rdate else rdate - @dt end
) X1
union all
select exchangerate, pref
from (
select top 1 exchangerate, 2 pref
from ExchangeRates
order by rdate
) Y
) T
order by pref

-- Steve Kass
-- Drew University
-- Ref: 250CBB08-57AE-45C7-97F2-AF26AFC368ED

Benny Chow wrote:[color=blue]
> Hello,
>
> I need help in writing a SQL statement in MS SQL Server 2000 to select
> the latest date (i.e., the date closest to or equal to the current date)
> for a given date.
>
> For example, in a table I have the following records:
> Date Exchange-Rate
> 01/Sep/03 0.55
> 05/Sep/03 0.59
>
> If the given date is 02/Sep/03, then the rate 0.55 should be return.
> If the given date is 03/Sep/03, then the rate 0.55 should be return.
> If the given date is 04/Sep/03, then the rate 0.59 should be return.
>
> Thanks in advanced,
>
> Benny
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

Jul 20 '05 #6

Erland Sommarskog

David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:[color=blue]
> Benny wants the closest, before or after the specified date according to
> his example.[/color]

Funny guy. :-) Some of our tables for prices and rates are sparse in a
similar manner, but we always assume that a value applies until a new
value comes in. So I assume he wanted the same.

But had Benny included CREATE TABLE and sample data in INSERT statements,
I would have seen that my solution was wrong!


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #7

David Portas

> Funny guy. :-) Some of our tables for prices and rates are sparse in a[color=blue]
> similar manner, but we always assume that a value applies until a new
> value comes in. So I assume he wanted the same.[/color]

I agree that it seems like an unusual requirement. Although I suppose if you
wanted to calculate the value of a currency deal retrospectively it might
make sense to take the closest rate as the best approximation. But IANAA.

[color=blue]
> But had Benny included CREATE TABLE and sample data in INSERT statements,
> I would have seen that my solution was wrong![/color]

I know that feeling! :|

--
David Portas
------------
Please reply only to the newsgroup
--

Jul 20 '05 #8

Benny Chow

Thanks for all your guys help ^^.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 20 '05 #9