473,287 Members | 3,228 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Get the closest date

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
8 39825
Benny Chow (be***@stg.net.nz) writes:
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.


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
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
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
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
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:
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 #6
David Portas (RE****************************@acm.org) writes:
Benny wants the closest, before or after the specified date according to
his example.


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #7
> 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.
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.

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


I know that feeling! :|

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #8
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Josh | last post by:
I need a query that will select the closest date. I have to tables Pricing and InventoryItem. tblInventoryItem InventoryItemID <- Pk Description tblPricing
1
by: sk | last post by:
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)...
6
by: KoRnDragon | last post by:
I know about getlastmod() but is there one for created date? If not is there some other way of getting the created date of a file?
2
by: CK | last post by:
Good Morning, I have a view that contains rate information, contractIDs, and effective dates. I need to select the rate info based on contractID and date. I can provide a date and contractID, and...
3
by: randy1200 | last post by:
I have a DataView (dv) that includes a StartDate DateTime column. I'd like to return the DataView record that contains a StartDate value closest to my IncidentDate value: string filter =...
2
by: josecruz | last post by:
I hope this make sense... When students leave school (dropout, medical reasons, etc), there is a date field that admission use to record this event. For program evaluation, I want to know what...
1
by: CatherineA | last post by:
I need help writing an SQL statement in Access 2007 to select the closest date/time. I have Spot Time (the date/time commercials ran) and Call Time (the date/time we received calls). I need to...
7
by: GraemeC | last post by:
I have a form (single record form) that loads records from a query that are sorted date order as the records are viewed in date order. Some dates are in the past and some are in the future. Currently...
10
reginaldmerritt
by: reginaldmerritt | last post by:
I'm trying to open a form where the value of a field has a date closest to today but not beyond todays date. I guess i need some sort of MAX function in a where statment maybe???? Any ideas how...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.