Forex Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Forex Query

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2011-04-16 : 12:09:11

Hi

WE have below table
create table forex (id int identity(1,1),from_currency varchar(20), to_currency varchar(20),rate float, date datetime)

insert into forex
select 'INR', 'USD', 45,GETDATE()-240 union all
select 'INR','USD',48,GETDATE()-35 union all
select 'INR','USD',44,GETDATE()-60 union all
select 'INR','USD',47,GETDATE()-80 union all
select 'INR','USD',49, GETDATE()-120

user will send three parameters, from currency, to currency and date.

i need a query if pass the current date
1. current rate will shown
2.if i pass last month, but the last month record is not there
query will shown the previous one

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-16 : 21:09:15

[code]DECLARE @from_currency VARCHAR(32),
@to_currency VARCHAR(32),
@date DATETIME;

SET @from_currency = 'INR';
SET @to_currency = 'USD';
SET @date = '20110101';

SELECT
TOP 1 rate
FROM
forex
WHERE
@from_currency = from_currency
AND @to_currency = to_currency
AND date < @date
ORDER BY
date DESC;[/code]

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-16 : 21:42:49

I believe the EU required\s that you use DECIMAL (x, 5) for rates and not FLOAT.

The use of an IDENTITY column makes no sense. You have no key!! We have a DATE data type now, so use it. Currency codes are fixed length CHAR(3) not VARCHAR(20). The final design flaw is that history tables have (start_date, end_date pairs)

CREATE TABLE FoirexHistory
(rate_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
rate_end_date DATE, --null is current
CHECK (rate_start_date < rate_end_date)
source_currency_code CHAR(3) NOT NULL,
destination_currency_code CHAR(3) NOT NULL,
CHECK (source_currency_code <> destination_currency_code),
forex_rate DECIMAL (10,5) NOT NULL
CHECK(forex_rate > 0.00000),
PRIMARY KEY (rate_start_date, source_currency_code, destination_currency_code);

Now just use

SELECT *
FROM ForexHistory
WHERE @in_rate_date BETWEEN rate_start_date AND rate_end_date
AND @in_source_currency_code = source_currency_code
AND @in_destination_currency_code = destination_currency_code;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources